Excel Interview Questions for Data Analysts
India 2026 — 30 Q&A from Beginner to Advanced

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column to the right. Its key limitations: (1) can only look to the right — the lookup column must be the leftmost; (2) breaks if you insert or delete columns (the column number is hard-coded); (3) defaults to approximate match — forgetting the fourth argument FALSE causes wrong results; (4) slightly slower than INDEX MATCH on large datasets.

=VLOOKUP(A2, EmployeeTable, 3, FALSE)
-- A2: lookup value | EmployeeTable: range | 3: column index | FALSE: exact match

XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX MATCH use cases. Key improvements: (1) lookup and return arrays are separate — no column number needed, no breakage on insert/delete; (2) can look left, right, up, or down; (3) returns a default value instead of #N/A when there is no match; (4) supports approximate, wildcard, and binary search modes. Use XLOOKUP whenever Microsoft 365 is available. Use INDEX MATCH when you need to support older Excel versions.

=XLOOKUP(A2, EmployeeID_col, Salary_col, "Not found")
-- A2: lookup value
-- EmployeeID_col: where to search
-- Salary_col: what to return
-- "Not found": default if no match

INDEX returns a value from a range by row/column position. MATCH returns the position of a value in a range. Together, INDEX(MATCH()) looks in any direction, does not break when columns are inserted, and is faster than VLOOKUP on large data because it evaluates only the lookup column rather than scanning the entire table array.

=INDEX(Salary_col, MATCH(A2, EmployeeID_col, 0))
-- MATCH(A2, EmployeeID_col, 0): finds row position of A2 (0 = exact match)
-- INDEX(Salary_col, ...): returns salary from that row

Use XLOOKUP with an array lookup, or INDEX MATCH with an array formula multiplying two MATCH conditions. The & concatenation trick joins both columns into a single key for VLOOKUP — but XLOOKUP with the * operator on arrays is cleaner.

-- XLOOKUP two-criteria:
=XLOOKUP(1, (Names=A2)*(Depts=B2), Salaries, "Not found")

-- INDEX MATCH two-criteria (Ctrl+Shift+Enter for array formula in older Excel):
=INDEX(Salaries, MATCH(1, (Names=A2)*(Depts=B2), 0))

VLOOKUP and XLOOKUP only return the first match. To return all matches use FILTER (Microsoft 365) or, in older Excel, an array formula with IFERROR + INDEX MATCH with SMALL + IF — though FILTER is far simpler.

-- FILTER (Microsoft 365):
=FILTER(SalaryRange, NameRange=A2, "No match")
-- Returns all rows where Name matches A2 as a spill array

SUMIF applies one condition. SUMIFS applies multiple conditions (all must be true — AND logic). In modern Excel, always use SUMIFS even for one condition — the argument order is more consistent (sum_range comes first).

-- SUMIF (one condition):
=SUMIF(Region_col, "North", Sales_col)

-- SUMIFS (multiple conditions):
=SUMIFS(Sales_col, Region_col, "North", Month_col, "March")

Use COUNTIF with a comparison operator wrapped in quotes as the criteria string.

=COUNTIF(Sales_col, ">50000")

-- With two conditions (COUNTIFS):
=COUNTIFS(Sales_col, ">50000", Region_col, "South")

Wrap the month criteria in TODAY() and MONTH() so the formula updates automatically.

=SUMPRODUCT(
  (MONTH(Date_col)=MONTH(TODAY())) *
  (YEAR(Date_col)=YEAR(TODAY())) *
  Sales_col
)

* matches any sequence of characters, ? matches any single character. Wildcards work in text criteria for SUMIF, SUMIFS, COUNTIF, COUNTIFS.

-- Sum all sales where product name contains "Pro":
=SUMIFS(Sales_col, Product_col, "*Pro*")

-- Sum where name starts with "A":
=SUMIFS(Sales_col, Name_col, "A*")

Divide the SUMIF for each region by the total SUM. Use absolute references for the total to allow the formula to copy down correctly.

=SUMIF(Region_col, A2, Sales_col) / SUM(Sales_col)
-- Format the cell as Percentage
-- A2 = region name; copy down for each region

Insert → PivotTable → select your data range → choose a sheet location. The four areas: (1) Rows — categories to group by on the Y axis; (2) Columns — cross-tabulation categories; (3) Values — what to aggregate (sum, count, average); (4) Filters — slicers for the entire pivot. A pivot table is the fastest way to summarise a large dataset without writing formulas.

Right-click the pivot table → Refresh. To auto-refresh on file open: PivotTable Analyze → Options → Data → check "Refresh data when opening the file". If you added new rows beyond the original range, you must also update the data source range first (PivotTable Analyze → Change Data Source).

A Calculated Field is a virtual column in the pivot that is computed from other fields in the Values area — you cannot reference cells outside the pivot. Example: add a "Profit Margin" calculated field = Revenue / Cost to show margin percentage within the pivot without adding a column to the source data.

PivotTable Analyze → Fields, Items & Sets → Calculated Field
Name: Profit Margin
Formula: = Revenue / Cost

Right-click any value cell in the pivot → Show Values As → % of Grand Total. This replaces the raw number with a percentage of the row or column grand total without changing the source data or writing formulas.

Click any date in the Row Labels → right-click → Group → select Months and Years → OK. Excel groups all dates automatically. If the option is greyed out, there are non-date values or blanks in the date column — clean those first.

GETPIVOTDATA extracts a specific value from a pivot table by field name rather than cell reference — so it stays correct if the pivot layout changes. Useful for building executive summary reports that pull specific KPIs from a pivot without breaking when rows are added.

=GETPIVOTDATA("Sales", $A$3, "Region", "North", "Year", 2025)
-- Extracts North 2025 Sales from the pivot rooted at A3

Dynamic array formulas (available in Microsoft 365 / Excel 2021+) return arrays that spill into multiple cells automatically — you enter the formula in one cell and it fills as many cells as needed. The range they occupy is called the spill range. Reference the whole spill range with the # operator: =A1#.

FILTER returns rows that match all conditions — AND logic using * between arrays, OR logic using +.

=FILTER(OrderTable,
  (Region_col="North") * (Sales_col>100000),
  "No results")
-- * = AND logic: both conditions must be true

UNIQUE() returns distinct values as a spill array — it replaces the old method of Advanced Filter + Copy to location.

=UNIQUE(City_col)
-- Sorted unique list:
=SORT(UNIQUE(City_col))

Combine SORT and FILTER or use LARGE with INDEX to create a report that recalculates as source data changes — no pivot refresh needed.

-- Sort all products by sales descending, take top 5:
=TAKE(SORT(ProductSalesTable, 2, -1), 5)
-- Column 2 = sales | -1 = descending | TAKE keeps first 5 rows

-- Alternatively with FILTER + LARGE:
=FILTER(Product_col, Sales_col >= LARGE(Sales_col, 5))

Power Query (Get & Transform) is a data cleaning and shaping tool built into Excel and Power BI. You apply transformation steps — remove duplicates, split columns, filter rows, merge tables — using a visual editor, and Power Query records every step. When your source data updates, you refresh the query and all steps re-run automatically. This eliminates the weekly manual cleaning that most analysts waste hours on.

Data → Get Data → From File → From Folder → select the folder → Combine & Transform. Power Query automatically stacks the sheets from all files in the folder into one table. Any new file added to the folder appears on the next refresh. This is one of the highest-value Power Query use cases for analysts who receive weekly reports from multiple regions.

Right-click the column header → Change Type → Date. If Power Query cannot detect the format automatically, use Transform → Date → Parse (with locale) or add a custom column using Date.FromText with the format argument.

Select the columns you want to keep (ID, Name, etc.) → right-click → Unpivot Other Columns. Power Query converts all remaining month columns into two columns: Attribute (the month name) and Value (the data). This is essential for loading monthly report data into Power BI or a database.

M (Mashup) is the functional language that Power Query uses internally — every step you create in the visual editor generates M code. Analysts do not need to write M fluently, but knowing how to read and edit it (via Advanced Editor) lets you fix broken steps, add parameters to queries, and create dynamic date filters that the visual editor cannot handle. Understanding M separates good Power Query users from expert ones.

TRIM removes leading, trailing, and extra internal spaces (keeps single spaces between words). For non-breaking spaces that TRIM misses, wrap with CLEAN or substitute the CHAR(160) character.

=TRIM(A2)
-- For stubborn spaces:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Use Text to Columns (Data → Text to Columns → Delimited → Space) for a one-time split. For a formula that stays live: LEFT/FIND for first name, MID/FIND for last name, or TEXTSPLIT (Microsoft 365).

-- First name:
=LEFT(A2, FIND(" ", A2) - 1)

-- Last name:
=MID(A2, FIND(" ", A2) + 1, LEN(A2))

-- TEXTSPLIT (Microsoft 365):
=TEXTSPLIT(A2, " ")   -- spills into two cells

For viewing duplicates: add a COUNTIF column — any row with count > 1 is a duplicate. For removing: Data → Remove Duplicates → select the columns to consider. For keeping the first occurrence and removing later duplicates programmatically, use Power Query (Home → Remove Rows → Remove Duplicates).

-- Flag duplicates (1 = duplicate):
=COUNTIF($A$2:$A2, A2) > 1
-- The expanding range $A$2:$A2 makes the first occurrence FALSE

Excel stores dates as serial numbers (1 = January 1, 1900). Select the cells → Format Cells → Date, or use TEXT() to convert to a string in a specific format.

=TEXT(A2, "DD-MM-YYYY")   -- as text string
-- Or format the cell as Date — keeps it as a true date value

Use VALUE with SUBSTITUTE to strip non-numeric characters, or use a combination of TEXT functions. In Microsoft 365, TEXTAFTER or TEXTSPLIT is cleaner.

-- Strip "INR " prefix and comma, convert to number:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "INR ", ""), ",", ""))

-- Microsoft 365:
=VALUE(TEXTAFTER(A2, "INR "))