Excel Error Handling in Formulas
Formula errors appear when Excel cannot complete a calculation. A cell shows a code like #DIV/0! or #VALUE! instead of a result. These errors are not failures — they are Excel telling you exactly what went wrong. This topic explains what each error means and how to handle or hide them cleanly.
The Common Excel Errors
| Error Code | Plain English Meaning | Common Cause |
|---|---|---|
| #DIV/0! | Divided by zero | =A1/B1 where B1 is empty or zero |
| #VALUE! | Wrong type of data | =A1+B1 where B1 contains text |
| #REF! | Cell reference is broken | Deleting a row or column a formula pointed to |
| #NAME? | Excel does not recognise the name | Typo in function name: =SUMM instead of =SUM |
| #N/A | Value not available | VLOOKUP cannot find the lookup value |
| #NUM! | Invalid number in formula | =SQRT(-4) — cannot take square root of a negative |
| #NULL! | Incorrect range operator | =SUM(A1:A5 C1:C5) — space instead of comma |
| ###### | Column too narrow to display the value | Widen the column — not a true error |
Diagram: How Errors Appear
+--------+--------+------------------+ | A | B | C | +--------+--------+------------------+ | 100 | 0 | =A1/B1 → #DIV/0! | | "abc" | 5 | =A2+B2 → #VALUE! | | 25 | | =A3/B3 → #DIV/0! | +--------+--------+------------------+
IFERROR — The Primary Error Handler
IFERROR wraps any formula and replaces an error result with something you specify — a message, a zero, or a blank.
=IFERROR(formula, value_if_error) =IFERROR(A1/B1, 0) → Shows 0 if division fails =IFERROR(A1/B1, "N/A") → Shows text "N/A" if division fails =IFERROR(A1/B1, "") → Shows nothing (blank) if division fails =IFERROR(VLOOKUP(D1, A:B, 2, 0), "Not Found")
IFERROR Diagram
WITHOUT IFERROR: +---------------------------+ | =A1/B1 → #DIV/0! | ← error is visible to user +---------------------------+ WITH IFERROR: +------------------------------------------+ | =IFERROR(A1/B1, "No Data") → No Data | ← clean display +------------------------------------------+
IFNA — Handle Only #N/A Errors
IFNA works like IFERROR but only catches the #N/A error. Use it with VLOOKUP or MATCH when a value is genuinely not found, but you still want to see other errors like #VALUE! or #REF!.
=IFNA(VLOOKUP(D1, A:B, 2, 0), "Not in list") If D1 exists in column A → returns the normal VLOOKUP result If D1 is NOT in column A → returns "Not in list" If VLOOKUP has a #VALUE! error (wrong data type) → still shows #VALUE!
ISERROR, ISERR, and ISNA — Check for Errors
These functions return TRUE or FALSE depending on whether a cell contains an error. Use them inside IF formulas for more control than IFERROR provides.
| Function | Returns TRUE When |
|---|---|
| ISERROR(A1) | A1 contains any error |
| ISERR(A1) | A1 contains any error except #N/A |
| ISNA(A1) | A1 contains specifically #N/A |
=IF(ISERROR(A1/B1), "Check your data", A1/B1) If A1/B1 produces an error → shows "Check your data" If A1/B1 works normally → shows the result
Fixing the #REF! Error
A #REF! error appears when you delete a row, column, or sheet that a formula referenced. Excel cannot update the reference because the source is gone.
Before deleting row 3: =SUM(A1:A5) ← works fine After deleting row 3 (which was part of A1:A5): =SUM(A1:#REF!) ← broken reference
Click the cell showing #REF! and look at the formula bar. Find the part that says #REF! and either retype the correct range or use Ctrl+Z to undo the deletion.
Fixing the #NAME? Error
The #NAME? error means Excel does not recognise something in your formula — almost always a typo in a function name or a text value missing its quotes.
=SUMM(A1:A5) → #NAME? — SUMM is not a function =SUM(A1:A5) → Correct =IF(A1=Yes, "OK", "No") → #NAME? — Yes needs quotes =IF(A1="Yes", "OK", "No") → Correct
Fixing the ##### Display
This is not an error. The column is simply too narrow to display the number. Widen the column by double-clicking the right edge of the column header to auto-fit, or drag the edge to make it wider.
Using Error Handling in VLOOKUP
VLOOKUP is a common source of #N/A errors when a search value does not exist in the lookup table. Wrap it with IFERROR to handle this neatly.
=IFERROR(VLOOKUP(F2, A:C, 3, 0), "Product not found") Customer searches for product code in F2. If the code exists in column A → returns the value from column C. If the code does not exist → displays "Product not found".
Error Handling Best Practices
| Situation | Recommended Approach |
|---|---|
| Hide all errors for a clean report | Use IFERROR with "" |
| Show zero when an error occurs | Use IFERROR with 0 |
| Handle only "not found" in lookups | Use IFNA instead of IFERROR |
| Debug which error type appears | Use ISERROR, ISERR, or ISNA in an IF |
| Track down a broken reference | Look for #REF! in the formula bar and retrace |
Error handling makes your spreadsheets professional. A report showing #DIV/0! throughout suggests incomplete work. The same report with clean blanks or meaningful messages communicates that the data has gaps — which is honest and clear. Good error handling protects your formulas from crashing when input data is incomplete or inconsistent.