Skip to content

Excel Interview Questions for Data Analytics

38 Excel questions with answers for data analytics roles — 15 easy, 20 medium, 3 hard.

About Excel

Excel rounds test lookups (XLOOKUP, INDEX-MATCH), PivotTables, SUMIFS, dynamic arrays like FILTER and UNIQUE, data validation, Power Query and the Data Model. Almost every analyst job in India still starts with Excel, so interviewers expect fast, accurate formulas and clean, well-structured workbooks that others can trust and reuse.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is data validation in Excel?

ExcelEasy
Save to ▾
Show answer
Data → Data Validation restricts what can be entered in a cell (lists, number ranges, dates), reducing manual entry errors in shared sheets.
02

SUMIFS vs COUNTIFS?

ExcelEasy
Save to ▾
Show answer
SUMIFS adds values that meet several conditions; COUNTIFS counts rows that meet several conditions. Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
03

What is the difference between COUNT, COUNTA and COUNTIF?

ExcelEasy
Save to ▾
Show answer
COUNT counts numbers, COUNTA counts non-empty cells and COUNTIF counts cells meeting one condition; COUNTIFS handles several conditions.
04

VLOOKUP vs INDEX-MATCH vs XLOOKUP?

ExcelEasy
Save to ▾
Show answer
VLOOKUP only looks to the right and breaks when columns are inserted. INDEX-MATCH looks in any direction and is robust. XLOOKUP is the modern option with exact match by default and left lookups.
05

What is a pivot table and when do you use it?

ExcelEasy
Save to ▾
Show answer
A pivot table summarises large data by dragging fields into rows, columns, values and filters — for example total sales by region and month — without writing formulas.
06

What does the IF function do and how do you nest it?

ExcelEasy
Save to ▾
Show answer
IF returns one result if a test is true and another if false. Nest IFs for multiple outcomes, or use IFS for cleaner formulas.
07

How do you remove duplicates in Excel?

ExcelEasy
Save to ▾
Show answer
Data tab, Remove Duplicates, pick the columns to compare. For a non-destructive way use UNIQUE() or conditional formatting to highlight repeats first.
08

What is conditional formatting?

ExcelEasy
Save to ▾
Show answer
It automatically formats cells based on rules such as colour scales, data bars or highlighting values above a target, making patterns visible at a glance.
09

Your FILTER formula shows #SPILL! instead of results. Why, and how do you fix it?

ExcelEasy
Save to ▾
Show answer
Dynamic array formulas such as FILTER, UNIQUE and SORT return several values that spill into neighbouring cells. #SPILL! appears when that spill range is blocked by existing data, merged cells, or when the formula sits inside an Excel Table, which does not allow spilling. Fix it by clearing the blocking cells, unmerging, or moving the formula outside the Table. Select the error cell to see the dashed border of the range it needs.
10

Write Excel formulas to get a sorted list of unique customer names and their count.

ExcelEasy
Save to ▾
Show answer
-- unique customers from column B, sorted A to Z =SORT(UNIQUE(B2:B1000)) -- number of unique customers =COUNTA(UNIQUE(B2:B1000)) -- unique customers sorted by total sales, highest first =SORTBY(UNIQUE(B2:B1000), SUMIFS(D2:D1000, B2:B1000, UNIQUE(B2:B1000)), -1)
11

How do you combine text from several cells with a separator in Excel?

ExcelEasy
Save to ▾
Show answer
-- join first and last name with a space =TEXTJOIN(" ", TRUE, A2, B2) -- all products of one order in a single cell, comma separated =TEXTJOIN(", ", TRUE, FILTER(C2:C500, A2:A500=F2)) -- older method =A2 & " " & B2 -- TRUE in TEXTJOIN skips empty cells, which CONCAT and & do not
12

What are named ranges in Excel and why are they useful?

ExcelEasy
Save to ▾
Show answer
A named range gives a cell or range a meaningful name, such as GST_Rate or SalesData, managed from the Name Manager. Formulas become readable, for example =Amount*GST_Rate instead of =B2*$H$1, and they do not break when you move the input cell. Names also work as data validation sources and in charts. For growing data, prefer Excel Tables, whose structured references expand automatically.
13

How do you calculate working days between two dates in Excel, excluding holidays?

ExcelEasy
Save to ▾
Show answer
-- A2 = start date, B2 = end date, Holidays = named range of holiday dates =NETWORKDAYS(A2, B2, Holidays) -- if the weekend is only Sunday (weekend code 11) =NETWORKDAYS.INTL(A2, B2, 11, Holidays) -- date that is 10 working days after A2 =WORKDAY(A2, 10, Holidays)
14

What does Flash Fill do in Excel, and when should you not rely on it?

ExcelEasy
Save to ▾
Show answer
Flash Fill (Ctrl+E) detects a pattern from one or two examples you type and fills the rest of the column, for example extracting first names or reformatting phone numbers. It is fast for one-time cleanup. However, it produces static values, not formulas, so it will not update when source data changes, and it can guess wrongly on irregular rows. For repeatable reports, use formulas or Power Query instead.
15

Tell me about a time you found an error in someone else's spreadsheet.

ExcelEasy
Save to ▾
Show answer
Situation: a teammate's incentive calculation sheet was about to go to payroll. Task: I was asked to review it. Action: I noticed totals did not match the sales extract, traced it to a SUM range that stopped at row 500 while data had grown to 640 rows, and told the teammate privately with the fix. Result: we converted the data to an Excel Table so ranges expand, and payouts went out correctly.
16

What is a PivotTable and when do you use it?

ExcelMedium
Save to ▾
Show answer
A tool that summarises large tables by dragging fields into rows, columns, values and filters, so you can slice sales by region or month without writing formulas.
17

What is the difference between a table and a normal range?

ExcelMedium
Save to ▾
Show answer
A table auto-expands, supports structured references, banded rows and slicers, and keeps formulas consistent as rows are added.
18

How do you remove duplicates in Excel but keep the latest record?

ExcelMedium
Save to ▾
Show answer
Sort by date (newest first), then Data → Remove Duplicates on the key column — Excel keeps the first occurrence. Or use UNIQUE / a COUNTIFS helper column.
19

What is the difference between Goal Seek and Solver?

ExcelMedium
Save to ▾
Show answer
Goal Seek changes one input to hit a target output; Solver optimises with several variables and constraints, for example maximising profit under budget limits.
20

What are absolute and relative cell references?

ExcelMedium
Save to ▾
Show answer
Relative references change when copied (A1); absolute references stay fixed ($A$1); mixed references lock only the row or column (A$1 or $A1).
21

Explain INDEX and MATCH together.

ExcelMedium
Save to ▾
Show answer
MATCH finds the position of a value in a range and INDEX returns the item at that position, giving a flexible lookup that works left or right and survives column insertions.
22

How do you create a dynamic dashboard in Excel?

ExcelMedium
Save to ▾
Show answer
Build PivotTables from a clean table, add PivotCharts and slicers, link KPIs with formulas, and refresh from Power Query so everything updates together.
23

How do you handle errors like #N/A and #DIV/0!?

ExcelMedium
Save to ▾
Show answer
Fix the root cause when possible, or wrap formulas with IFERROR or IFNA to return a clear fallback value.
24

How do SUMIFS and SUMPRODUCT differ?

ExcelMedium
Save to ▾
Show answer
SUMIFS sums values meeting several criteria and is fast. SUMPRODUCT multiplies arrays and adds them, handling more complex conditions and weighted totals.
25

What is the difference between a calculated field and a helper column in Excel?

ExcelMedium
Save to ▾
Show answer
A calculated field lives inside a PivotTable and works on summarised data; a helper column is a normal column of formulas that feeds the PivotTable or other formulas.
26

What do the match_mode and search_mode arguments of XLOOKUP control?

ExcelMedium
Save to ▾
Show answer
match_mode decides how a value is matched: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, and 2 wildcard. search_mode decides direction: 1 searches first to last, -1 searches last to first, and 2 or -2 use binary search on sorted data. Searching last to first is a quick way to fetch the latest entry for a customer without sorting, and next-smaller matching handles tax slabs or commission bands.
27

How does the FILTER function work in Excel? Write a formula to list all North region orders.

ExcelMedium
Save to ▾
Show answer
-- A2:D500 holds Order, Customer, Region, Amount =FILTER(A2:D500, C2:C500="North", "No orders") -- two conditions (AND) multiply the tests =FILTER(A2:D500, (C2:C500="North")*(D2:D500>10000), "No orders") -- OR conditions add the tests =FILTER(A2:D500, (C2:C500="North")+(C2:C500="East"))
28

What is the LET function in Excel and why would you use it?

ExcelMedium
Save to ▾
Show answer
LET assigns names to intermediate results inside a single formula, for example =LET(sales, SUM(D2:D100), cost, SUM(E2:E100), (sales-cost)/sales). It makes long formulas readable and faster, because a repeated sub-calculation is evaluated once instead of several times. It is especially useful with dynamic arrays, where the same FILTER result might otherwise be recalculated three or four times in one nested formula.
29

What is Power Pivot and the Excel Data Model?

ExcelMedium
Save to ▾
Show answer
The Data Model is an in-memory columnar store inside Excel that holds several related tables, and Power Pivot is the interface to manage it. Instead of flattening data with VLOOKUPs, you load fact and dimension tables, create relationships and write DAX measures. It handles millions of rows that would not fit on a sheet, and PivotTables built on it can slice across tables. It is the same engine idea that Power BI uses.
30

Write an Excel formula to put invoices into ageing buckets (0-30, 31-60, 61-90, 90+ days).

ExcelMedium
Save to ▾
Show answer
-- C2 = invoice date, TODAY() = as-of date =LET(age, TODAY()-C2, IFS(age<=30, "0-30", age<=60, "31-60", age<=90, "61-90", TRUE, "90+")) -- then a PivotTable with bucket in rows and SUM of amount gives the ageing report
31

What is a Data Table in Excel's What-If Analysis?

ExcelMedium
Save to ▾
Show answer
A Data Table shows how a formula's result changes when one or two inputs vary. For example, place a loan EMI formula in a corner, list interest rates down the column and tenures across the row, then use What-If Analysis, Data Table to fill every combination. It is ideal for sensitivity analysis in pricing or budgeting. Large Data Tables slow recalculation, so set calculation to automatic except tables when needed.
32

You share a forecasting workbook and users keep overwriting formulas. How do you protect it?

ExcelMedium
Save to ▾
Show answer
Separate input cells from calculation cells and colour inputs clearly. Unlock only the input cells through Format Cells, Protection, then protect the sheet so formulas cannot be edited, and optionally hide formulas. Add data validation on inputs to block wrong values such as negative quantities. Protect workbook structure so sheets cannot be deleted, keep a version history, and add a short instructions sheet so users know where to type.
33

Tell me about a time you automated a manual Excel task.

ExcelMedium
Save to ▾
Show answer
Situation: our team spent about four hours every Monday copying sales exports from five branches into one summary sheet. Task: I was asked to cut that time. Action: I built a Power Query that read every file in a shared folder, cleaned headers and appended them, then a PivotTable and a few formulas produced the summary on refresh. Result: the report took ten minutes, copy-paste errors stopped, and two other teams reused the template.
34

Write an Excel formula to look up a price using two criteria, product and city.

ExcelMedium
Save to ▾
Show answer
=XLOOKUP(1, (A2:A500=G2) * (B2:B500=H2), C2:C500, "Not found") -- A = product, B = city, C = price -- G2 and H2 hold the product and city to find -- each condition returns TRUE/FALSE; multiplying gives 1 only where both match
35

You inherit a monthly Excel report and suspect some numbers are hard-typed over formulas. How do you audit it?

ExcelMedium
Save to ▾
Show answer
Use Go To Special and select Constants with Numbers to highlight hard-typed values inside formula areas. Show Formulas (Ctrl+`) to scan columns for inconsistencies, and use Trace Precedents and Dependents on key totals. Inquire or Spreadsheet Compare, if available, can flag inconsistent formulas. Then fix the overwritten cells, protect formula ranges and add check totals that compare with the source data each month.
36

How would you reconcile two large lists in Excel?

ExcelHard
Save to ▾
Show answer
Use XLOOKUP or COUNTIF to flag missing items in each list, or merge them in Power Query with a full outer join and filter unmatched rows.
37

Your workbook has 200,000 rows and hundreds of VLOOKUPs and takes minutes to recalculate. What do you do?

ExcelHard
Save to ▾
Show answer
First switch calculation to manual to work safely, then find the cause. Replace whole-column references with Tables, swap repeated VLOOKUPs for one XLOOKUP or INDEX-MATCH helper column, and remove volatile functions like OFFSET, INDIRECT and TODAY used in many cells. Better still, move the joins into Power Query or the Data Model so lookups happen once at refresh. Finally paste values for historic data that never changes and keep formulas only where needed.
38

What is the LAMBDA function in Excel and when would you use it?

ExcelHard
Save to ▾
Show answer
LAMBDA lets you write a custom, reusable function with formulas only, no VBA. You define it, for example =LAMBDA(price, rate, price*(1+rate)), and save it in the Name Manager as AddGST, then call =AddGST(B2, 18%) anywhere. Helper functions like MAP, REDUCE, SCAN and BYROW pair with it for array logic. Use it when a complex calculation repeats across a workbook, so the logic lives in one tested place.

Frequently asked questions

How important is Excel in Data Analytics interviews?

Excel rounds test lookups (XLOOKUP, INDEX-MATCH), PivotTables, SUMIFS, dynamic arrays like FILTER and UNIQUE, data validation, Power Query and the Data Model. Almost every analyst job in India still starts with Excel, so interviewers expect fast, accurate formulas and clean, well-structured workbooks that others can trust and reuse.

How should I use the Excel sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.