Top 50 Excel interview questions with answers

1. How to answer any Excel question

Definition → when to use → small example → one trap. Example: "XLOOKUP finds a value in one column and returns from another. I use it for price lookups. =XLOOKUP(A2, Codes, Prices, "Not found"). Unlike VLOOKUP it defaults to exact match and can look left."

A. Basics (1–10)

1. Workbook vs worksheet? A workbook is the file; worksheets are the tabs inside it.
2. Relative, absolute, mixed references? A1 changes when copied, $A$1 never changes, $A1/A$1 lock only the column/row. F4 cycles through them.
3. Formula vs function? A formula is anything starting with =; a function is a built-in like SUM used inside a formula.
4. Maximum rows and columns? 1,048,576 rows and 16,384 columns (A to XFD).
5. Why use an Excel Table (Ctrl + T)? It expands automatically, uses readable structured references, keeps headers visible and feeds pivots/Power Query reliably.
6. How does Excel store dates? As serial numbers (1 = 1-Jan-1900); time is the decimal part. That's why dates can be added and subtracted.
7. Uses of Paste Special? Values only, formats, transpose, skip blanks, and operations (e.g. multiply by 1 to turn text numbers into numbers).
8. Freeze Panes? View → Freeze Panes keeps header rows/columns visible while scrolling.
9. Data Validation? Restricts what can be typed — dropdown lists, number ranges, dates — to prevent bad data at entry.
10. Example of conditional formatting? Highlight overdue invoices, duplicates, top 10 values, or show data bars and icon sets for status.

B. Formulas and lookups (11–24)

11. VLOOKUP limitations? Lookup value must be in the first column, can't look left, column number breaks when columns are inserted, and it defaults to approximate match — use FALSE/0 for exact matching.
12. Why INDEX + MATCH? It can look left, doesn't break when columns move, and works in older Excel.
13. XLOOKUP advantages? Exact match by default, looks left or right, built-in "if not found", can search from the bottom, returns multiple columns.
14. When is approximate match useful? Slabs — tax, commission, grades — with the lookup table sorted ascending by the lower limit.
15. SUMIF vs SUMIFS? One condition vs many. In SUMIFS the sum range comes first.
16. COUNT vs COUNTA vs COUNTBLANK? Counts numbers / non-empty cells / empty cells.
17. IFERROR vs IFNA? IFERROR hides every error (can hide real bugs); IFNA hides only #N/A — safer around lookups.
18. Common error values? #N/A not found · #VALUE! wrong type · #REF! deleted reference · #DIV/0! · #NAME? misspelt function or name · #SPILL! blocked spill range · #NUM! invalid number.
19. Nested IF vs IFS vs SWITCH? IFS lists condition/result pairs without nesting; SWITCH matches one value against exact options; nested IF works everywhere but gets hard to read.
20. What does TEXT do? Formats a number or date as text, e.g. =TEXT(A2,"dd-mmm-yyyy"). The result is text, so don't use it for further maths.
21. Extract the first name? =LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ") - 1) or =TEXTBEFORE(TRIM(A2)&" ", " ") in Microsoft 365. Flash Fill (Ctrl + E) for one-off jobs.
22. Difference between two dates? Days: =B2-A2. Complete years: =DATEDIF(A2,B2,"y"). Working days: NETWORKDAYS.
23. What is SUMPRODUCT for? Multiplying arrays and adding the results — e.g. total value =SUMPRODUCT(Qty, Price) — and multi-condition counts in older Excel.
24. What are dynamic arrays? Formulas like FILTER, UNIQUE, SORT that return many results and "spill" into neighbouring cells; refer to the whole result with A2#.

C. Data cleaning (25–32)

25. Remove duplicates? Data → Remove Duplicates on the key columns (check first with COUNTIF > 1), or UNIQUE / Power Query for repeatable cleaning.
26. Numbers stored as text? Use the green-triangle "Convert to Number", VALUE, multiply by 1, or Text to Columns → Finish.
27. Extra spaces? TRIM removes extra spaces; CLEAN removes non-printing characters; web data may need SUBSTITUTE(A2, CHAR(160), " ").
28. Split a column? Text to Columns, Flash Fill, TEXTSPLIT, or Power Query's Split Column.
29. Dates imported wrongly (dd/mm vs mm/dd)? Text to Columns with date format DMY, or Power Query "Change type using locale" English (India).
30. Find duplicates without deleting? Conditional Formatting → Duplicate Values, or a helper =COUNTIF($A$2:$A$500, A2) > 1.
31. Fill blank cells with the value above? Select range → Go To Special → Blanks → type = and the cell above → Ctrl + Enter → then paste as values.
32. Combine 12 monthly files? Power Query → From Folder → Combine; next month just refresh.

D. Pivots and charts (33–40)

33. What is a pivot table? A tool to summarise large data by dragging fields into Rows, Columns, Values and Filters — no formulas needed.
34. Pivot doesn't show new data? Refresh (Alt + F5); if the source is a fixed range, change it to an Excel Table so it grows.
35. Group dates by month in a pivot? Right-click a date → Group → Months (and Years). "Cannot group" means blanks or text in the date column.
36. Show % of total? Value Field Settings → Show Values As → % of Grand Total (or % of Parent, Running Total, Rank…).
37. Calculated field trap? It works on sums, not rows — fine for ratios like Profit/Sales, wrong for row-level logic like commission slabs.
38. Slicer vs filter? Slicers are visual buttons, show what's selected, and can control many pivots through Report Connections.
39. Which chart when? Trend → line · comparison → bar/column · part of whole (2–4 parts) → stacked bar/pie · distribution → histogram · relationship → scatter.
40. When to use a secondary axis? Only when two series have different units (₹ and %). Never for two series in the same unit.

E. Power tools (41–46)

41. What is Power Query? A recorded, repeatable data-cleaning tool: import, transform, load — then refresh next time.
42. Merge vs Append? Merge joins tables side by side on a key (like XLOOKUP); Append stacks tables with the same columns.
43. What is unpivot? Turning month columns into rows (Region, Month, Value) so data becomes analysable.
44. What is the Data Model / Power Pivot? A built-in database that holds multiple related tables (millions of rows) and lets you write DAX measures.
45. Measure vs calculated column? A column is calculated per row and stored; a measure is calculated per pivot cell based on filters — use measures for totals and ratios.
46. Excel or Power BI? Excel for flexible analysis, models and small-team reports; Power BI for large data, scheduled refresh in the cloud and sharing dashboards with many users.

F. Automation and scenarios (47–50)

47. What is a macro? Recorded or written VBA code that repeats steps; save as .xlsm; enable only from trusted sources.
48. A workbook is slow — what do you check? Volatile functions (OFFSET, INDIRECT, TODAY), full-column references in array formulas, repeated lookups, too many conditional formats, calculation mode; move heavy data to Power Query/Data Model.
49. Goal Seek vs Data Table vs Scenario Manager? Goal Seek finds the one input that gives a target result; a Data Table shows results for many input values; Scenario Manager saves named sets of inputs.
50. How do you reconcile two lists (e.g. bank vs books)? Create a matching key, use XLOOKUP/COUNTIF both ways (or Power Query merge with Left Anti) to find items missing on each side, then check amount differences for matched items.

Common mistakes

Memorising definitions without an example. Saying "I know VLOOKUP" but not its limitations. Claiming skills you can't demonstrate in a practical test.