Error Handling With IFERROR and Safer Alternatives Today

A sheet full of #N/A and #DIV/0! looks broken, and the fastest repair is to wrap every formula in IFERROR(..., 0). The errors vanish, the report looks clean and the totals are wrong in a way that nobody sees. This is one of the most common faults in inherited workbooks, and it deserves more care than a wrapper. This article covers the seven error values, what each one tells you, and the handlers that fix a specific cause without hiding the rest. It builds on Excel formulas data people use every week, and the related error types seen in imported files are in understanding Excel errors when data comes out.

The error values and what they mean

Each error is a diagnosis, if you read it.

Newer builds add #SPILL!, #CALC! and #GETTING_DATA for dynamic arrays and connected data. The error is stored as a value in the file (t="e"), so a reader sees exactly the same code.

The point of the table is the third column. Different errors need different responses: a #DIV/0! is often legitimate (no sales this month), a #N/A is often expected from a lookup, and a #REF! or #NAME? is always a defect in the workbook.

What IFERROR does

=IFERROR(value, value_if_error)

returns value unless it is any of the errors above, in which case it returns the fallback. That covers all of them, which is the problem. Consider:

=IFERROR(VLOOKUP(A2, Customers!A:C, 3, FALSE), "unknown")

If the key is missing, the cell shows "unknown", which is right. If someone deletes column C from Customers, the formula becomes #REF!, and the cell still shows "unknown" for every row. The report now claims that no customer is known, and nothing flags the change. The wrapper converted a loud failure into a quiet lie.

IFNA for lookups

IFNA(value, value_if_na) catches only #N/A.

=IFNA(XLOOKUP(A2, Customers!A:A, Customers!C:C), "unknown")

A missing key returns "unknown"; a deleted column still produces #REF!, which you see. For lookups, IFNA is almost always the right wrapper. With XLOOKUP you do not need it at all, since the fourth argument is the not-found value:

=XLOOKUP(A2, Customers!A:A, Customers!C:C, "unknown")

This is cleaner, and it ignores nothing else (XLOOKUP examples that replace VLOOKUP pain). TEXTBEFORE, TEXTAFTER and FILTER have equivalents in their last arguments.

Test the cause instead of catching the result

For a division, test the divisor:

=IF(B2 = 0, "", A2 / B2)

This answers the question "what should happen when there is no base?", and it leaves other errors visible: if A2 contains text, you still see #VALUE!. Choose the fallback with thought. Blank ("") is a text string and breaks later arithmetic (#VALUE! when added). 0 is a number that may be misleading (a 0% rate when the truth is "not applicable"). NA() returns #N/A on purpose, which charts skip and SUM rejects, so use it where you want a visible gap.

For an empty cell check, =IF(ISBLANK(A2), ..., ...) or =IF(A2 = "", ...) (the latter also catches empty strings). For type checks, ISNUMBER, ISTEXT and ISERR (all errors except #N/A) give precise tests:

=IF(ISNUMBER(A2), A2 * 1.2, "check input")

This reports bad input in words and leaves the formula's other behavior alone.

ISERROR and friends

ISERROR(x) is true for any error, ISNA(x) only for #N/A, ISERR(x) for any except #N/A. They are the older style of IFERROR and are used in tests across a range:

=SUMPRODUCT(--ISERROR(D2:D500))

counts the errors in a column, which is the quickest way to measure a problem before deciding how to treat it. Put that count on the sheet next to the total ("12 rows with errors excluded") so that the exclusion is a visible fact.

Summing around errors

One error in a range makes SUM return that error. Wrapping each cell in IFERROR is one fix, and AGGREGATE is a cleaner one:

=AGGREGATE(9, 6, D2:D500)

Function number 9 is SUM, and option 6 means "ignore error values". It works on a range without helper cells. Other function numbers cover average (1), count (2), max (4), min (5) and more, and options 6 and 7 control hidden rows and errors. In a range where errors indicate missing data, silently ignoring them biases the result, so report the count of ignored cells beside the total.

SUMIF and SUMIFS skip rows that do not meet the condition, which can be used to avoid errors in other rows, but they still return an error if the sum range contains an error in a matching row. SUMIF(D2:D500, "<>#N/A") is a known idiom to sum while excluding #N/A cells.

Errors inside arrays and dynamic formulas

IFERROR works on arrays too, replacing errors element by element: =IFERROR(A2:A100 / B2:B100, 0) returns an array with zeros where the division failed. In dynamic array formulas, FILTER returns #CALC! for an empty result, and its third argument handles that case; do not use IFERROR for it, or you also hide spill errors (dynamic array functions FILTER, SORT and UNIQUE).

Imported files carry cached errors

A workbook you load into a database may contain cells with cached error values: the string #N/A, #REF! or #DIV/0! is the stored value. A loader reading a numeric column then hits text and fails or converts to null. Decide how to treat them: reject the file, convert to null with a count in the log or stop and ask the owner to fix the source. Do not coerce silently to zero (understanding Excel errors when data comes out and the #REF!, #NAME? and #VALUE! errors go through the load-side behavior).

A policy that works

A small set of rules for a team's workbooks:

1. **Do not wrap a formula in IFERROR until you know which error you expect.** Use IFNA for lookups and tests for divisors.

2. **Never use IFERROR(..., 0) on a number that will be summed** unless zero is the true answer.

3. **Leave #REF! and #NAME? alone.** They mean the workbook is broken, and you want them seen.

4. Count the errors you suppress and show the count on the sheet.

5. Document the fallback next to the formula: what "unknown" or blank means.

6. Conditional formatting can highlight any error cell in red as a safety net: use the formula =ISERROR(A1) over the data range (spotting data entry errors with conditional formatting).

When hiding an error is the wrong response

Hiding an error is wrong whenever the error carries information the reader needs: a missing price, a broken link, a type that failed to convert. It is wrong in reports that feed other systems, where a zero looks like a real value. It is wrong in audited workbooks, where a reviewer expects to see exceptions, not their absence. And it is wrong as a way of making a demo look good. Handle the expected cases precisely, and let the unexpected ones show. Microsoft's pages for IFERROR and AGGREGATE give the full argument lists.