Handling errors and blanks
8 minutes · 4 steps · free, no sign-up
One bad cell can spoil a whole report. A divide by zero turns into an error, and any total that touches it fails too. You will meet the common errors and learn to replace them with something sensible, without hiding real mistakes.
- Recognise #DIV/0! and #N/A
- Replace an error with a message using IFERROR
- See how text results are ignored by AVERAGE
- Use IFNA for lookups and ISBLANK to test for empty cells
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=IFERROR(B2/C2, "no target") catch any error
=IFNA(VLOOKUP(...), "not found") catch only #N/A
=ISBLANK(C4) TRUE if the cell is empty
=ISERROR(x) =ISNUMBER(x) =ISTEXT(x)The key ideasSummary: Handling errors and blanks
Errors are the sheet telling you something is wrong. #DIV/0! means you divided by zero or an empty cell, and #N/A means a lookup found nothing. IFERROR(formula, fallback) shows the fallback instead of the error, and because text is ignored by AVERAGE and SUM, the rest of your report keeps working.
Be careful: IFERROR hides every error, including a typo. IFNA only catches "not found", so other mistakes still show. ISBLANK checks for an empty cell before you rely on it.
Watch out forCommon mistakes
- Wrapping everything in IFERROR. It hides real mistakes such as a misspelt range. Use it where you expect a specific problem.
- Replacing an error with 0. A zero is counted in averages and totals. Text such as "n/a" is skipped.
- Using ISBLANK on a space. A cell holding a single space is not blank. TRIM or clear it.
- Fixing the symptom, not the cause. If a lookup is always #N/A, the key may have a stray space. Check the data too.
Next stepTry it in the real Google Sheets
IFERROR, IFNA, ISBLANK and ISERROR work the same in Excel. In Google Sheets, a small red corner on a cell and the error tooltip explain what each error means.