Fix the Spreadsheet: Excel Debugging Test
10 broken formulas · 15 minutes · The traps interviewers set
Every sheet here has a formula that gives the wrong answer. Find the bug and fix the formula in the bar below the sheet; it starts with the broken version so you can edit it. Press Enter to check.
Other tests: general practice test · data analyst · finance · accountant · MIS executive · Indeed-style · Kenexa-style · SHL-style · TestGorilla-style · fix the spreadsheet
The 10 bugs that break Excel formulas
Most wrong answers in real spreadsheets come from a short list of mistakes. Interviewers know them, which is why "here's a broken sheet, fix it" is a favourite interview task. This test covers:
- Lookups: approximate-match VLOOKUP, hidden trailing spaces and the wrong column number.
- References: a total that slides when the formula is copied, because the $ signs are missing.
- Ranges: a SUM that misses the last row, and SUMIF arguments in the wrong order.
- Data types: numbers stored as text, and comparing a number with "50" in quotes.
- Maths and errors: missing brackets in percent change, and #DIV/0! that needs IFERROR.
Each answer comes with a plain explanation of the bug. For more on the formulas themselves, see the VLOOKUP interview questions, the TRIM function guide and the percentage formula guide.
Frequently asked questions
What is an Excel debugging test?
Instead of writing a formula from scratch, you get a sheet where a formula gives the wrong answer and you fix it. Interviewers use this to see whether you understand why formulas break, not just how to type them.
Why does my VLOOKUP return #N/A when the value is in the list?
Usually the lookup value doesn't match exactly: a trailing space, a number stored as text, or a typo. Clean the value with TRIM or VALUE, and make sure the last argument is FALSE for an exact match.
Why does SUM return 0?
The numbers are probably stored as text, often after importing a CSV or copying from a website. Excel shows a green triangle in the corner of those cells. Convert them with Text to Columns, VALUE or SUMPRODUCT(--range).
Why does my formula break when I copy it down?
Relative references move when copied. Lock ranges that should stay fixed, such as a total, with dollar signs: $B$2:$B$5.
Does any working fix count?
Yes. Any formula that returns the right result and uses cell references counts, just as an interviewer would accept any correct fix.