Concept
1. Understand first, hide later
An error is Excel's way of telling you something is wrong. Hiding every error with IFERROR is a bad habit — understand the cause first.
2. Common error codes
| Error | Meaning | Common cause | Fix |
|---|---|---|---|
#DIV/0! |
division by zero | divisor cell is 0 or blank | check the data, or use IF/IFERROR |
#N/A |
value not found | lookup item doesn't exist, spelling/space mismatch | match the data, or use IFNA |
#VALUE! |
wrong type of data | multiplying text by a number, different-size ranges in SUMIFS | fix the data type |
#REF! |
broken reference | the cell/column the formula pointed to was deleted | rebuild the formula or Undo |
#NAME? |
Excel doesn't recognise a name | misspelled function, text without quotes | fix spelling/quotes |
###### |
not an error | column is too narrow | widen the column |
3. Create each error yourself (practice)
=10/0→#DIV/0!="abc"*5→#VALUE!=SUMM(A1:A5)→#NAME?- Type
=B1*2in A1, then delete column B →#REF!
Click an error cell — a small warning icon appears next to it with "Help on this error" and "Show Calculation Steps".
4. IFERROR
=IFERROR(value, value_if_error)
Run the formula; if it errors, show the second value instead.
Profit margin where sales may be 0:
=IFERROR(C2/B2, 0)
Or to show blank: =IFERROR(C2/B2, "")
The AVERAGEIFS from Lesson 6 where nothing matches:
=IFERROR(AVERAGEIFS(E2:E8, D2:D8, "Camera"), "No sales")
5. IFNA — catches only #N/A
=IFNA(value, value_if_na)
IFERROR hides every error — even a typo's #NAME? and a broken #REF!. That hides real mistakes.
IFNA catches only #N/A (the normal "not found" case in lookups), so other errors stay visible and you can fix them. This will be very useful in the Lookup module (XLOOKUP/VLOOKUP).
6. When to use which
| Situation | Use |
|---|---|
| Item not found in a lookup is expected | IFNA |
| Division by zero is expected (new products, blank months) | IFERROR or IF(B2=0, 0, C2/B2) |
| You don't know why the error appears | Don't hide anything — debug first |
7. IF vs IFERROR for #DIV/0!
=IF(B2=0, 0, C2/B2)
=IFERROR(C2/B2, 0)
Both give the same result. The IF version is safer because it only handles the zero case; if B2 accidentally contains text, #VALUE! will show and you'll know.
Common mistakes
Wrapping every formula in the sheet with IFERROR — reports quietly start showing wrong zeros. Hiding an #N/A caused by an extra space or a number stored as text instead of understanding it. Treating ###### as an error and changing the formula.