How to Use the ISERR Function in Excel to Catch Errors While Ignoring Missing Data

When building complex Microsoft Excel spreadsheets, calculation errors like #DIV/0! or #VALUE! are inevitable. While many users rely on the popular ISERROR function to catch these issues, it is often too broad. ISERROR catches everything, including #N/A errors, which typically just indicate missing data rather than a broken formula. To specifically identify calculation failures while ignoring missing data flags, you must use the ISERR function.

Why Use ISERR Instead of ISERROR?

The ISERR function is designed to return a TRUE value for almost all Excel errors (such as #REF!, #NAME?, and #NUM!), with one critical exception: it returns FALSE for #N/A. This distinction is vital when using lookup functions like VLOOKUP. An #N/A from a VLOOKUP simply means the search term wasn’t found (which might be expected behavior), whereas a #REF! means your formula is structurally broken.

Step 1: Understand the Syntax

The syntax for the function is extremely straightforward: =ISERR(value).

• value: This is the cell reference, formula, or expression you want to test for an error.

Step 2: Test a Basic Calculation Error

Let’s use ISERR to identify a classic division by zero error.

1. In cell A1, type the number 10.
2. In cell B1, type the number 0.
3. In cell C1, type the formula =A1/B1. This will result in a #DIV/0! error.
4. In cell D1, type the formula =ISERR(C1).
5. Press Enter. Cell D1 will display TRUE because a mathematical error occurred.

Step 3: Test an #N/A Error

Now, let’s observe how ISERR handles a missing data error differently than other error types.

1. In cell A2, type the formula =NA() to intentionally generate an #N/A error (simulating a failed VLOOKUP).
2. In cell B2, type the formula =ISERR(A2).
3. Press Enter. Cell B2 will display FALSE.

Step 4: Combine ISERR with the IF Function

The most practical use of ISERR is combining it with an IF statement to display custom warning messages only when actual formulas break.

1. Select an empty cell.
2. Type the following formula: =IF(ISERR(A1/B1), "Formula Broken", "Calculation OK")
3. If B1 is zero, Excel will display “Formula Broken.” However, if the calculation results in an #N/A (perhaps because the cells reference an empty dataset), the formula will not trigger the warning message.

By switching to the ISERR function, you can build smarter error-checking mechanisms that distinguish between broken spreadsheet logic and harmless missing data.