Excel Interview Questions — With Answers¶
Excel questions appear in virtually every data analyst interview, regardless of how senior the role. These range from basic formula knowledge to Power Query and advanced analytical patterns.
Tags: #Excel #InterviewPrep #DataAnalysis Level: Beginner → Senior
Fundamentals¶
[Beginner] What is the difference between VLOOKUP and XLOOKUP?
Show answer
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Direction | Left-to-right only — lookup column must be the leftmost | Any direction — lookup and return can be any column |
| Not found | Returns #N/A by default |
Specify a custom not-found value |
| Match mode | Approximate by default (common bug source) | Exact by default |
| Return column | Integer index (fragile — breaks when columns shift) | Direct reference (robust) |
| Multiple results | Returns one value | Can return a range (spill) |
| Version | All Excel versions | Excel 365 / Excel 2021+ only |
=VLOOKUP(A2, Products!A:D, 3, FALSE) -- return column 3, exact match
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found") -- cleaner, safer
When to use VLOOKUP: when you need to support Excel 2016 or earlier users. Otherwise, always use XLOOKUP.
[Beginner] What does $ mean in a cell reference? When do you use absolute vs relative references?
Show answer
$ locks a reference so it doesn't change when the formula is copied.
| Reference | Type | Behaviour when copied |
|---|---|---|
A1 |
Relative | Row and column both change |
$A1 |
Mixed (column locked) | Column stays A, row changes |
A$1 |
Mixed (row locked) | Row stays 1, column changes |
$A$1 |
Absolute | Neither changes |
Business example: Tax rate is in cell B2. You calculate =A5 * B2. When you copy this formula down for 100 products, A5 correctly shifts to A6, A7... but B2 also shifts — wrong! Use =A5 * $B$2 to lock the tax rate cell.
[Beginner] What is a Pivot Table and what can you use it for?
Show answer
A Pivot Table is an interactive summary tool that aggregates and reorganises data without changing the source. You drag fields to rows, columns, values, and filters.
Common analytics uses: - Sum/count/average by category - Revenue by region × product - Month-over-month comparison - Top/bottom N items
Interview answer tip: Always mention what Pivot Tables can't do well — they don't handle messy/unnormalised data, they're manual (not automated), and they break when source data structure changes.
[Mid-level] What is the difference between INDEX MATCH and XLOOKUP?
Show answer
INDEX MATCH was the professional's alternative to VLOOKUP before XLOOKUP existed. It's more flexible than VLOOKUP but more verbose than XLOOKUP.
-- INDEX MATCH: return value from col C where col A matches A2
=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))
-- XLOOKUP: same thing, cleaner
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")
XLOOKUP is simpler to read and write. INDEX MATCH is still valuable when: - You need compatibility with Excel 2016 and earlier - You need to return a range of values (INDEX can return a full row or column) - You need two-dimensional lookups (INDEX with two MATCH functions)
[Mid-level] How does SUMIF work? Write a formula that sums revenue for completed orders only.
Show answer
SUMIF(range, criteria, sum_range) — sums values in sum_range where the corresponding value in range matches criteria.
=SUMIF(D:D, "completed", G:G)
-- D = status column, G = revenue column
-- Multiple conditions: SUMIFS
=SUMIFS(G:G, D:D, "completed", C:C, "Electronics")
-- Sum G where D="completed" AND C="Electronics"
Interview extension: SUMIFS is much more common in practice. Know it well. AVERAGEIFS and COUNTIFS follow the same pattern.
[Mid-level] What is Power Query and when would you use it instead of formulas?
Show answer
Power Query is Excel's built-in ETL tool (Extract, Transform, Load). Available in Excel 2016+.
Use Power Query when: - You need to combine data from multiple files or sheets - You need to clean and transform data (split columns, remove blanks, pivot/unpivot) - The process will be repeated — Power Query steps are recorded and re-run with one click on refresh - The source data is external (SQL database, SharePoint, web)
Use formulas when: - The transformation is simple and one-off - You need the calculation to be live/dynamic (Power Query requires a manual refresh)
Interview tip: mention that Power Query's steps are reproducible and auditable — this is a major advantage over manual formula-based cleaning.
[Mid-level] What is the difference between a formula and a function in Excel?
Show answer
- A function is a built-in operation:
SUM,VLOOKUP,IF,TEXT - A formula is an expression starting with
=that can contain functions, operators, and cell references
=SUM(A1:A10) is a formula that uses the SUM function.
=A1 + B1 is a formula that uses no function (just an operator).
In interviews, use "formula" for what goes in the cell (=SUM(A:A)) and "function" for the specific operation (SUM, XLOOKUP).
[Senior] You have 12 monthly Excel files from different regional teams, each with slightly different column orders and naming. You need to combine them into one clean table every month. How do you approach this?
Show answer
Power Query approach (recommended):
- Place all monthly files in one folder
- In Excel: Data → Get Data → From Folder → select the folder
- Power Query loads all files and presents them as a combined query
- Add transformation steps: standardise column names (rename), reorder columns, remove blanks
- Load to a table
Next month, add the new file to the folder and click Refresh All — Power Query re-runs the same steps automatically.
Key advantage: the process is automated and reproducible. No copy-paste errors.
If Power Query isn't available: use a macro (VBA) to loop through files, copy data to a master sheet, and apply column mappings. Document the mapping table.
For production use: push this to a proper ETL tool or Python script rather than Excel — Excel's refresh is manual and error-prone.
[Senior] What is the difference between VLOOKUP's approximate and exact match modes, and why is the default mode a common source of bugs?
Show answer
VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
range_lookup = TRUE(or omitted): approximate match — assumes the first column is sorted ascending, returns the largest value ≤ lookup_value. Used for tax brackets, salary bands.range_lookup = FALSE: exact match — finds the exact value, returns#N/Aif not found.
Why the default is a bug source:
The default is TRUE (approximate match). If a developer forgets the 4th argument, Excel doesn't raise an error — it silently returns a wrong result if the data isn't sorted. A product ID lookup against an unsorted list will match the wrong product without any error message.
Quick-Fire Questions¶
[Beginner] What does #DIV/0! mean and how do you handle it?
Show answer
Division by zero. Wrap with IFERROR: =IFERROR(A1/B1, 0) or =IF(B1=0, 0, A1/B1).
[Beginner] How do you count cells that contain text?
Show answer
=COUNTA(A:A) — counts all non-blank cells.
=COUNTIF(A:A, "completed") — counts cells equal to "completed".
=COUNTIF(A:A, "*@gmail*") — counts cells containing "@gmail" (wildcard).
[Mid-level] How do you highlight duplicate values in a column?
Show answer
Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values. Or use a formula rule: =COUNTIF($A:$A,A1)>1.
[Mid-level] What is a named range and why is it useful?
Show answer
A named range gives a cell or range a descriptive name (e.g., "TaxRate", "ProductList"). Use it in formulas: =A1 * TaxRate instead of =A1 * $B$2. More readable, easier to update, self-documenting.
[Senior] How would you build a dynamic dashboard in Excel that updates when you change a slicer selection?
Show answer
- Create a PivotTable from the data with the metrics you need
- Create PivotCharts from the PivotTable
- Insert Slicers (Insert → Slicer) and connect them to the PivotTable
- Arrange charts and slicers on a dedicated "Dashboard" sheet
- For non-pivot chart linked KPIs, use GETPIVOTDATA or structured table references that update with the pivot