Excel / Spreadsheets interview questions
Excel / Spreadsheets questions a Data analyst / BI can be asked, from first job to senior. Open one to see the strongest answer and why it works.
You drag a formula down a column and every row after the first one is wrong. What is going on?
The strongest answer: The reference to the rate cell moved down with the formula — I lock it as $B$1 so it stays put, and leave the row reference relative so it walks down the column
Why it works: Exactly — the $ locks what must not move. Knowing which half to lock is the whole trick.
- Lock what must not move.
- $ before the column, the row, or both.
- Leave the walking reference relative.
- Drag, then check the last row.
Excel turned long order ids into scientific notation. What happened?
The strongest answer: It guessed a number and dropped precision — I keep ids as text, and I do not “fix” them by rounding
Why it works: Exactly. Excel is eager. Identifiers are text. Rounding an id is destroying it.
- Ids are text.
- Excel will guess number.
- Do not round an id.
- Do not store ids as floats.
A CSV of dates opens as 44927 in Excel. What do you do?
The strongest answer: Treat it as Excel’s serial and convert with a known origin — or stop using Excel as the source of truth for dates
Why it works: Exactly. Serials are a display. They are not ids and not years.
- Know the Excel origin.
- Convert or stop using Excel as source.
- Serial ≠ id.
- Do not invent a year from a serial.
How do you total last month’s paid orders by region in a sheet, and what goes wrong first?
The strongest answer: SUMIFS — the sum range first, then each criteria range with its criterion, every range the same height; I point the date criteria at cells rather than typing a date inside the formula
Why it works: Right — same-shaped ranges and criteria that live in cells are what make it re-runnable next month.
- Put criteria in cells, not in text.
- Sum range first in SUMIFS.
- Keep every range the same height.
- Change the cell, not the formula.
VLOOKUP cannot find a key that is sitting one column to the left. What is the lesson?
The strongest answer: VLOOKUP only looks right — use INDEX/MATCH or XLOOKUP, or better, do the join in SQL where the grain is visible
Why it works: Exactly. The tool has a direction. Two copied columns are how Excel becomes a second warehouse.
- Know the lookup direction.
- Prefer a visible SQL join.
- Do not duplicate columns as a habit.
- Excel is not the warehouse.
A VLOOKUP returns a value for an id that is not in the lookup table at all. How is that possible?
The strongest answer: The last argument was left out, so it did an approximate match and returned the nearest lower key; I pass FALSE for an exact match, or use XLOOKUP, which is exact by default
Why it works: Exactly — the fourth argument defaults to approximate, and an unsorted column then hands back a silent wrong answer.
- Always ask for an exact match.
- Pass FALSE, or use XLOOKUP.
- Approximate match needs a sorted column.
- Decide what a missing key shows.
A pivot does not show a new row you just typed. What do you check?
The strongest answer: The source range and whether the cache refreshed — Excel will happily analyse last Tuesday’s rectangle
Why it works: Exactly. Pivots are a cached extract of a range. Hand-edited totals are the next lie.
- Check the source range.
- Refresh the cache.
- Do not hand-edit totals.
- A pivot is not live SQL.
A pivot shows Count of Amount instead of Sum, and switching it to Sum gives a total that is too low. What is wrong?
The strongest answer: Some amounts are stored as text, so the field defaulted to Count and Sum now treats those cells as zero; I convert the column at the source and reconcile the total before publishing
Why it works: Right — Count is the tell. Text amounts drop out of the sum silently, so the reconcile matters as much as the fix.
- Count means text in the column.
- Convert at the source, not the view.
- Sum treats text as zero, silently.
- Reconcile the total before publishing.
Every month you spend a day turning four exported files into one clean table: delete the top rows, split a column, fix the types, drop duplicates.
How do you stop paying that day again next month?
The strongest answer: Do it once in Power Query — the steps are recorded and re-run on refresh, so next month’s files drop into the same folder and the table rebuilds itself, with the raw exports untouched
Why it works: Exactly — a query is a readable, repeatable transformation, and the applied steps are its documentation.
- Record the transformation, not the result.
- Clean once, in a query.
- Refresh replaces the manual day.
- Keep the raw exports untouched.
The monthly board pack comes out of a workbook four people edit, and last quarter a number changed between the draft and the meeting with nobody able to say who changed it or why.
What do you put in place?
The strongest answer: One owner, the file in storage that keeps version history, inputs pulled in by query instead of pasted, and a check for cells where a formula was overtyped with a constant
Why it works: Right — history, a named owner and a hunt for hardcoded cells are what make a spreadsheet answerable.
- A number must be answerable.
- Name one owner.
- Keep version history, not copies.
- Hunt overtyped formulas before sending.
Critical ops run on a shared Excel the author left. What do you do first?
The strongest answer: Stabilize it, name an owner, and plan a certified home — I do not yank it on Friday or pretend it is not production
Why it works: Exactly. It is production. Yank and forever and paper are the failure modes.
- Treat it as production.
- Owner now.
- Plan a certified home.
- No Friday yank.