Excel Interview Scenarios, Solved in Excel

An Excel interview rarely asks “what does VLOOKUP do” on its own; it asks you to solve a small, realistic problem and watches which functions you reach for. Interview scenarios are exactly that: short, practical situations that test whether you can combine two or three ordinary functions correctly, notice when a formula is fragile, or explain why a common shortcut gives the wrong answer in an edge case.

This guide works through eight such scenarios. Each one states the problem the way an interviewer might phrase it, then builds and runs the actual formula in Microsoft Excel, so every result below is what Excel really returned, not a predicted one.

1. Find the top performer by name, not just the top score

The question: “Given a list of names and their sales, write one formula that returns the name of the top performer – not the highest number, the name next to it.” MAX alone only gives the number; the name has to be looked up separately using that number as the search key, with INDEX and MATCH:

MATCH finds the position of the highest value inside B2:B5, and INDEX reads the name at that same position from A2:A5. The same pattern answers “who has the lowest score” by swapping MAX for MIN, and works for any column pair, not just two adjacent ones.

2. Find records missing from a second list

The question: “You have a list of expected attendees and a separate list of who actually showed up. Which formula flags everyone who is missing?” COUNTIF checks whether each name from the first list appears anywhere in the second:

Column E shows a more direct version of the same idea: =FILTER(A2:A6,COUNTIF(C2:C3,A2:A6)=0) spills only the missing names into a single list, with no helper column of Present/Missing labels needed at all. Both formulas rest on the same fact – COUNTIF returning 0 means “not found anywhere in that range.”

3. Will your total formula survive a new row of data?

The question: “Your SUM formula sits just below a list. Six months later someone adds one more row of data at the bottom. Does the total update itself?” The honest answer is: it depends entirely on whether that list is a plain range or an Excel Table. Starting from the same three-row list either way:

Plain range: what the sheet shows

Plain range: what is inside the cells

Typing a fourth item directly into row 5, right below the list, with no Insert command involved at all:

The total is still 17, and the formula itself is still literally =SUM(B2:B4); Excel has no way to know the list “grew”, because nothing about the reference itself changed. Now the same experiment with the list turned into a Table first (Insert, Table), summed with a structured reference instead of a cell range:

Table named Stock: what the sheet shows

Table named Stock: what is inside the cells

Nothing else changed between the two experiments except the presence of a Table. A Table automatically grows to include a new row typed directly beneath it, and every structured reference to that Table (Stock[Qty], a total row, a chart, a PivotTable source) grows with it. This is the single strongest practical argument for turning any list that will keep gaining rows into a Table early, covered in more depth in Tables, Sorting and Filtering.

4. Rank a leaderboard so ties don’t leave a gap

The question: “RANK.EQ gives two students who tied for first place a rank of 1 each, but then jumps straight to 3 for whoever comes next, skipping 2 entirely. How would you rank them 1, 1, 2 instead, with no gap?” RANK.EQ alone cannot do this; it needs to be replaced with a formula that counts how many distinct, strictly higher scores exist:

=SUMPRODUCT(($A$2:$A$6>A2)/COUNTIF($A$2:$A$6,$A$2:$A$6))+1 counts, for each score, how many distinct values beat it, treating a tied group as a single step rather than one step per member. It is a genuinely different question from RANK.EQ’s “how many rows have a higher or equal value”, and interviewers use exactly this pair of formulas to see whether a candidate notices the difference.

5. Work out a deadline that skips weekends

The question: “A task starts on 1 October 2026 and takes 10 working days. What date does it finish on, assuming Saturdays and Sundays don’t count?” Plain addition (start date + 10) would land on a calendar date that includes two weekends; WORKDAY is built specifically to skip them:

WORKDAY takes an optional third argument listing specific holiday dates to skip as well as weekends, and its counterpart NETWORKDAYS answers the reverse question – given a start and an end date, how many working days sit between them.

6. Pull the last value from a column that keeps growing

The question: “A column of daily figures keeps growing every day, with blank cells left below it for future entries. Write a formula that always returns the most recent (last non-blank) value, without changing the formula every day.” A plain reference to the last row would return a blank most of the time. Two different working approaches, over data in A1:A4 with A5:A10 left empty:

=LOOKUP(2,1/(A1:A10<>""),A1:A10) is the classic version of this trick: dividing 1 by an array of TRUE/FALSE values turns every filled cell into 1 and every blank into a #DIV/0! error, and LOOKUP’s last-match behaviour quietly ignores the errors and lands on the last real 1. The newer =XLOOKUP(TRUE,A1:A10<>"",A1:A10,,0,-1) does the same job more readably, searching from the bottom up (its last argument, -1, means “search last to first”) for the first TRUE it finds.

7. Build a sentence that updates itself

The question: “Write a single cell that always reads something like ‘Total sales: 1,28,450.50’, staying correct as the total changes, without anyone having to retype the sentence.” The trap is that concatenating a number directly with & shows its raw, unformatted value; TEXT() controls exactly how the number is displayed inside the sentence:

Without the TEXT() wrapper, ="Total sales: "&B1 would show the unformatted number exactly as stored (128450.5), with no thousands separator and only the one decimal place that happened to be entered. TEXT() is what lets a formula-built sentence look deliberately formatted rather than just concatenated.

8. Clean inconsistent names in a single formula

The question: “A column of names was pasted in from three different systems: some are in all caps, some have extra leading or trailing spaces, some have double spaces in the middle. Fix all of it with one formula, without a separate step for each problem.” The three problems need three different functions, nested together:

Working from the inside out: SUBSTITUTE replaces any double space with a single space, TRIM then removes any leading or trailing spaces left over, and PROPER capitalises the first letter of each word. The order matters – TRIM on its own only removes leading, trailing and repeated spaces it can already see, so an interior double space has to be collapsed by SUBSTITUTE first.

Common mistakes

Mistake 1: assuming any range formula automatically grows

A plain range reference only ever changes size when an actual insert or delete operation touches it. Typing new data into a previously blank cell just below a range, with no Table involved, leaves every formula that reference that range completely unaware anything changed – confirmed above with the Table comparison. It gets worse at the edges: inserting a brand-new row exactly at the first row of a summed range (rather than somewhere inside it) shifts the whole reference down and past the new row instead of expanding to include it – run for real, 17 stayed the total of the original three rows even after a row was inserted right above them, because the formula became =SUM(B3:B5), not =SUM(B2:B4) plus the new row.

Mistake 2: using RANK.EQ when a no-gap rank is actually wanted

RANK.EQ answers “how many scores are higher than or equal to this one”, which is the right definition for a sports leaderboard but the wrong one whenever the requirement is really “number the distinct tiers, 1, 2, 3, with no gaps”.

Mistake 3: concatenating a number without TEXT()

& always shows a number’s raw underlying value, ignoring any number format applied to the cell it came from. Any concatenated sentence that includes a number needs TEXT() around that number to control how it actually reads.

Mistake 4: nesting text-cleaning functions in the wrong order

PROPER, TRIM and SUBSTITUTE each solve a different problem and don’t fix each other’s mess automatically; TRIM does not collapse a double space in the middle of text, only leading, trailing and repeated spaces are handled specifically by what TRIM defines as extra spacing between words (single spaces are preserved, but it does collapse multiple spaces between words too) – the safest habit is to run SUBSTITUTE for a known specific pattern first, then TRIM as a general cleanup pass afterward.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A products list has an Excel Table applied to it, named Catalog, with a Price column. Someone types a new product directly into the row right below the Table. Does a total of =SUM(Catalog[Price]) elsewhere on the sheet include the new product without being edited?

Show solution

Yes. Typing into the row directly beneath a Table makes the Table absorb that row automatically, and every structured reference to it, including SUM(Catalog[Price]), grows to match without needing to be touched.

2. Four runners score 10, 10, 8, 6 seconds faster than the baseline (higher is better). Using the no-gap SUMPRODUCT pattern from this guide, what ranks would the two tied runners at 10 receive?

Show solution

Both would receive rank 1 (no one scored higher than either of them), and the next runner at 8 would receive rank 2, not 3 – the tie takes up one rank position, not two.

3. A cell holds =”Balance: “&B1 where B1 is formatted as currency and contains 2500. What does the cell actually display, and why might that surprise someone?

Show solution

It displays “Balance: 2500″, the raw unformatted number, ignoring B1’s currency formatting entirely, because & always reads a cell’s underlying value rather than what it looks like on screen. TEXT(B1,”format”) would be needed to keep the currency formatting inside the sentence.

Frequently asked questions

Why do Excel interviews focus on scenarios instead of asking about individual functions?

A scenario shows whether a candidate can combine functions to solve a problem that does not map to a single textbook formula, which is closer to what using Excel at work actually involves.

Why doesn’t my SUM formula pick up a new row I just added?

A plain range reference like SUM(B2:B4) only changes size when Excel performs an actual insert or delete operation on rows inside it. Typing new data into a blank cell just outside the range does not count as an insert, so the reference stays exactly as it was.

What is the real difference between RANK.EQ and a no-gap rank?

RANK.EQ counts how many values are higher than or equal to a given one, so tied values share a rank and the next distinct value jumps ahead by the size of the tie. A no-gap (dense) rank counts distinct higher values instead, so the ranks always run 1, 2, 3 with no skipped numbers.

Why does WORKDAY sometimes give a different answer than just adding days?

Plain addition counts every calendar day, including weekends. WORKDAY specifically counts only working days, skipping Saturdays and Sundays (and any holiday dates passed as its third argument).

What does XLOOKUP’s -1 search mode actually do?

It searches the range from the last item to the first, so the first match XLOOKUP finds is the last one in the original order, which is exactly what is needed to pull the most recent entry from a growing column.

Why does TRIM alone not fix a double space in the middle of a name?

TRIM removes leading and trailing spaces and collapses repeated spaces between words down to a single space. If the goal is to remove a specific pattern before that general cleanup, SUBSTITUTE is used first to target it directly.