Keep spoken answers short and concrete: what the function does, one example, and the mistake to avoid. If you can, say how you would check the result.
Basic questions
For any job that uses Excel, including admin and entry-level roles.
- What is the difference between a relative and an absolute reference?
A relative reference like
Practise it: Lock it with $A1shifts when you copy the formula; an absolute one like$A$1stays put. You lock the cell every row needs, such as a tax rate or a lookup table.- What is the difference between COUNT, COUNTA and COUNTBLANK?
Practise it: Count what's thereCOUNTcounts cells with numbers,COUNTAevery cell that isn't empty, text included, andCOUNTBLANKthe empty ones.- Why use SUM instead of typing the total?
A formula follows the data. A total typed in from a calculator is right until one number changes, and whoever checks the file can see it isn't a formula.
Practise it: Add it up- How do you work out a percentage of a total?
Divide each line by the total, lock the total with
Practise it: Percent of a total$so it doesn't slide when you fill down, and format the result as a percentage:=B2/$B$7.- How do you calculate a percent change?
New minus old, divided by old:
Practise it: Up or down by how much?=(C2-B2)/B2. A negative result is a fall.- How would you show "Pass" or "Fail" from a score?
Practise it: Pass or fail?=IF(B2>=50, "Pass", "Fail"). Say how you would check the edge: someone on exactly 50 should pass, so it is>=, not>.- How do you sort a table without mixing up the rows?
Click one cell in the column to sort by, then Data → Sort. If you select just that column, Excel shows a Sort Warning with "Expand the selection" chosen; picking "Continue with the current selection" sorts that column on its own, and every row ends up with someone else's number.
Practise it: Sort without breaking rows- How do you filter a list, and why might the filter miss rows?
Ctrl + Shift + L or Data → Filter. An empty row ends the list, so rows below it aren't filtered; select the whole list first.
Practise it: Filter to what matters- Which keyboard shortcuts do you use most?
Name a few you really use, for example Ctrl + arrow to jump to the end of the data, Ctrl + Shift + arrow to select it, Ctrl + D to fill down and F4 to add the
Excel shortcuts, with drills$.
Intermediate questions
For analyst, finance, accounting and operations roles.
- VLOOKUP, XLOOKUP or INDEX MATCH: which do you use?
XLOOKUP when the file is on Excel 2021 or 365: it can look left, matches exactly by default and has a built-in "not found" value. INDEX MATCH when the file has to work in older versions. VLOOKUP when you maintain a sheet that already uses it, with
Practise it: XLOOKUP, the modern wayFALSEas the last argument.- Why would a VLOOKUP return #N/A when the value is clearly there?
Usually one of four things: a missing
Practise it: Mistake hunt: #N/A everywhereFALSE, a stray space, a number stored as text on one side, or a table range that wasn't locked and slid down when the formula was copied.- What does the last argument of VLOOKUP do?
Practise it: Find the right bracketFALSEfinds an exact match.TRUEfinds the band a number falls into, such as a commission rate, and needs the first column sorted smallest first.- SUMIF or SUMIFS?
SUMIFS takes any number of conditions, SUMIF one. The argument order differs: SUMIF puts the range to add up last, SUMIFS puts it first.
Practise it: Add up with two conditions- How do you count rows that meet two conditions?
Practise it: Count with two conditionsCOUNTIFS, with a range and a condition for each:=COUNTIFS(B2:B13, "North", C2:C13, "Shipped"). For "either of two values", add two counts.- When would you use IFERROR, and when not?
To show a clean message where a missing value is expected, such as a code that isn't on the list. Not around a whole formula to make errors go away, because it hides real mistakes too.
Practise it: Catch the error, not the bug- How would you find which items are in one list but not the other?
Practise it: Which invoices are missing?COUNTIFeach item against the other list and flag the ones that return 0, or look each one up and flag the not-found results.- Why might a total be a cent off?
The cells show rounded numbers but store more decimals, and the total adds what is stored.
Practise it: What you see vs what's storedROUND(…, 2)on each line changes the stored value; a 0.00 format only changes the look.- Why does SUM ignore some of the numbers?
They are probably numbers stored as text: they sit on the left of the cell, often with a green triangle. SUM skips text. Convert them, for example with
Practise it: Mistake hunt: numbers stored as textVALUEor Text to Columns.- Pivot table or SUMIFS?
A pivot table is quicker to build and rearrange, but needs a refresh when the data changes. SUMIFS updates by itself and can sit anywhere in a report. Check that a pivot says Sum, not Count.
Practise it: Your first pivot table
Advanced questions
For senior analyst and modelling roles, or tests labelled "advanced".
- How do you write one formula that fills a whole table, right and down?
Mixed references: lock the column of the values down the side (
Practise it: Lock just the row or the column$A2) and the row of the values across the top (B$1), then fill in both directions.- Nested IF, IFS or a lookup table for bands?
For two or three bands, IFS or a nested IF reads fine. For more, put the bands in a small table and look them up with
Practise it: Grade bands with nested IFVLOOKUP(…, TRUE)or XLOOKUP, so changing a band means editing a cell, not every formula.- How do you look up a value to the left of the search column?
VLOOKUP can't.
Practise it: INDEX and MATCH togetherINDEX(return_range, MATCH(value, search_range, 0))can, in any version, and so can XLOOKUP in Excel 2021 or 365.- How do you get a list of unique values, and count them?
Practise it: One of each, then the totals=UNIQUE(B2:B13)spills the distinct list into the cells below;=COUNTA(UNIQUE(B2:B13))counts it. In older versions, Remove Duplicates on a copy or a pivot table.- What does #SPILL! mean?
A formula that returns a list, such as UNIQUE, needs empty cells to spill into, and one of them has something in it. Clear it, or move the formula.
Practise it: One of each, then the totals- How would you find which category grew the most?
Total each category per period, work out the change in a column, then name the row with the largest increase:
Practise it: Which category went up most?INDEX(names, MATCH(MAX(changes), changes, 0)). "Grew most" and "changed most" are different questions.- How do you build a sheet someone else can check?
Inputs such as rates in labelled cells, no numbers typed into formulas, one formula filled down each column, and steps in separate columns rather than one long formula.
Practise it: Mistake hunt: hard-coded numbers