Basic to Advance Excel with many use cases for Interview

Explore how the match function returns the position of a lookup value within a data range, enabling column and row positions. See examples for department position three and Rajesh 28.

Master vlookup in Excel to extract data vertically using a lookup value, table array, and column index with exact match; learn its left-column restriction and simple error handling with iferror.

Master vlookup and match for data extraction, including swapping left data with choose and dynamic columns. Apply freezing, exact/approximate matches, and error handling for clean results.

Learn how the hlookup function performs horizontal lookups in Excel, using a lookup value to fetch data across headers with four parameters, range lookup options, and practical steps.

Learn to use hlookup with match to extract data across multiple rows and columns, freezing rows, setting table arrays, and retrieving names and departments dynamically.

Learn to extract data from multiple data sets using HLOOKUP and MATCH to pull data across horizontal and vertical views, with IFERROR handling and header freezing.

Discover how the index function retrieves data from a range using row and column numbers, with examples for names and departments and a preview of automating with match.

Explore using index with match to retrieve department or id from either side of the lookup value. See how fixed columns and array references compare to vlookup and hlookup.

Explore index with multiple match to pull department, total salary, and ID by matching on names and headers, enabling data retrieval from both left and right of the lookup column.

Apply index and match to pull data from multiple data sets using order ID, with left-side and right-side lookups and iferror for second data sets.

Learn how to use the basic sum product function in Excel to calculate total cost from unit price and quantity, with practical steps using data ranges and arrays.

Learn to apply countifs and sumproduct functions in Excel to count with multiple criteria across departments, gender, ages, and dates, and compare outputs in parallel.

Explore how sumif and sumproduct functions compute regional sales and profit by single criteria, comparing results and mastering criteria ranges and sum ranges in Excel.

Explore the sumifs and sumproduct functions to compute sales and profit under region and product category conditions, using ifs logic, data validation, and freezing for accurate reporting.

Explore advanced sumproduct techniques to count present, leave, and absent across multi-row, multi-column data. Compare sumproduct with countifs and countif, enabling per-employee results when data spans rows and columns.

Learn to apply averageif and averageifs in Excel by using range, criteria, and average range to compute averages for single and multiple conditions, including region, product category, and year.

Explore advanced use of countifs, averageifs, and sumifs with if functions on a single data set to produce department and gender based calculations.

Utilize pivot tables in Excel to summarize, analyze, and present large data sets, rearranging fields to show sales by year, month, and department with dynamic calculations.

Demonstrates how to group data in a pivot table by year, month, and quarter, and convert the report to a tabular layout with fields like order date, sales, and profit.

Explore how to use slicers to filter data in pivot tables and pivot charts, selecting product category, date, and ship mode to analyze regional sales and profits.

Explore creating a calculated field in a pivot table to add tax as a percentage of profit, including a conditional tax: 10% above three lakhs, 5% otherwise.

Learn to create dynamic charts in Excel by defining dynamic ranges with the offset function, converting tables, and updating region, sales, and profit data automatically.

Learn how to use Excel's advanced filter to extract data with complex criteria, choose to filter in place or copy to another location, and obtain unique records.