Error Handling & Formula Auditing
Learn what each Excel error means, hide them gracefully with IFERROR and IFNA, and trace formulas to find where a number went wrong.
What you will learn
- Recognise common error values like #N/A and #DIV/0!
- Replace errors with friendly text using IFERROR and IFNA
- Trace and check a formula with auditing tools
Errors are messages, not disasters
When Excel cannot work something out, it puts a short error value in the cell, starting with a #. These are not crashes — they are clues telling you what went wrong. Before a spreadsheet is shared, an analyst is expected to understand them and handle them tidily.
| Error | It means | Common cause |
|---|---|---|
#N/A | Not available — not found | A lookup could not find the value |
#DIV/0! | Divide by zero | Dividing by an empty or zero cell |
#VALUE! | Wrong kind of value | Maths on text, like ="a"*2 |
#REF! | Reference gone | A cell the formula needed was deleted |
#NAME? | Name not recognised | A typo in a function or range name |
IFERROR: show a friendly message instead
A bare #N/A in a report looks broken to a non-technical reader. IFERROR wraps a formula and says: “try this; if it produces any error, show this instead.” You give it the formula, then the fallback to show on error.
=IFERROR(VLOOKUP("Mango", A2:B4, 2, FALSE), "Not found")Note: Output: Not found There is no Mango in the table, so VLOOKUP returned #N/A. IFERROR caught that error and displayed Not found — a clear message a manager understands, instead of a cryptic code.
IFERROR is not just for lookups — it catches any error. A very common one is dividing by an empty or zero cell, which gives #DIV/0!. Wrap the division in IFERROR and supply a sensible fallback (often 0) to show instead:
=IFERROR(B2/C2, 0)Note: Output: If C2 is 0, the result would be #DIV/0!, so IFERROR shows 0 instead. If C2 is 4 and B2 is 20, it simply shows 5. IFERROR only steps in when there is an error; when the maths works, it returns the real answer.
IFNA: catch only “not found”
Sometimes you want to hide a missing-lookup #N/A but still see other errors (like a #DIV/0! that signals a real bug). IFNA is the narrower tool — it catches only #N/A and lets every other error show through.
=IFNA(XLOOKUP("Mango", A2:A4, B2:B4), "Not in list")Note: Output: Not in list IFNA replaced the #N/A because Mango is missing. But if the formula had a different error, IFNA would let it appear — so genuine bugs are not accidentally hidden. Use IFNA for lookups and IFERROR when any error should be tidied away.
Watch out: Do not over-use IFERROR. Wrapping everything in IFERROR(..., 0) can hide real mistakes — a broken formula silently shows 0 and you never notice. Use it where an error is expected (like an item not in a list), and fix the cause everywhere else.
Auditing: find where the number went wrong
When a result looks wrong, formula auditing tools (on the Formulas tab) help you trace it back:
- Trace Precedents draws arrows from the cells that feed into the selected formula — “what does this depend on?”
- Trace Dependents draws arrows to the cells that rely on the selected cell — “what would I break if I changed this?”
- Evaluate Formula steps through a complex formula one piece at a time, showing each intermediate result so you can spot exactly where it goes wrong.
A quick manual trick: click the cell, look at the formula bar, and click each reference inside it — Excel highlights that range on the sheet so you can confirm it points where you expect.
Tip: To see every formula on a sheet at once (instead of their results), press Ctrl + ` (the grave-accent key, top-left of most keyboards). Press it again to switch back. It is the fastest way to scan a sheet for a stray hard-coded number hiding among the formulas.
Q. A VLOOKUP shows #N/A because the item is not in the table. Which is the cleanest fix for a report?
✍️ Practice
- Make a lookup that searches for a missing item, then wrap it in IFERROR to show “Not found”.
- Create a divide-by-zero situation and use IFERROR to show 0, then use Trace Precedents on a formula to see what feeds it.
🏠 Homework
- Build a small price-lookup sheet, wrap the lookup in IFERROR so missing products show “No price”, and add one IFNA example. Then use Evaluate Formula to step through one of your formulas and describe what each step does.