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:

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.