How to Use ISERROR in Excel
ISERROR is a logical function that checks whether a cell or formula result contains any error value. It returns TRUE if an error exists and FALSE if the value is valid. The syntax is straightforward:
Key Insights
- ISERROR detects all seven Excel error types and returns TRUE/FALSE, making it essential for building robust formulas that handle calculation failures gracefully
- While ISERROR combined with IF was the standard error-handling approach, IFERROR (introduced in Excel 2007) provides cleaner syntax and better performance for most use cases
- Use specific error checkers like ISNA or ISERROR only when you need granular control over different error types—otherwise, IFERROR should be your default choice
Understanding the ISERROR Function
ISERROR is a logical function that checks whether a cell or formula result contains any error value. It returns TRUE if an error exists and FALSE if the value is valid. The syntax is straightforward:
=ISERROR(value)
Error handling matters because spreadsheets rarely exist in isolation. Your formulas depend on user input, data imports, and calculations that can fail in numerous ways. A single #DIV/0! error can cascade through dependent cells, breaking dashboards and reports. ISERROR gives you programmatic control over these failures.
Here’s the most basic implementation:
=ISERROR(A1)
If cell A1 contains any error value, this returns TRUE. If A1 contains a number, text, or even a blank, it returns FALSE. Simple, but powerful when combined with other functions.
The Seven Error Types ISERROR Catches
ISERROR detects every error type Excel produces. Understanding what triggers each error helps you build better error-handling logic:
#DIV/0! - Division by zero. Occurs when a formula divides by zero or an empty cell.
=10/0 → #DIV/0!
=A1/B1 → #DIV/0! (if B1 is empty or zero)
#N/A - Value not available. Common with lookup functions when no match exists.
=VLOOKUP("Product Z", A1:B100, 2, FALSE) → #N/A (if Product Z doesn't exist)
#VALUE! - Wrong data type. Happens when you perform math operations on text.
=A1+B1 → #VALUE! (if A1 contains "text" and B1 contains 5)
#REF! - Invalid cell reference. Occurs when you delete rows/columns that formulas reference.
=SUM(A1:A10) → #REF! (if you delete column A)
#NUM! - Invalid numeric value. Appears when calculations produce numbers too large/small for Excel or invalid function arguments.
=SQRT(-1) → #NUM!
#NAME? - Excel doesn’t recognize text in a formula. Usually a typo in function names.
=SUMM(A1:A10) → #NAME? (should be SUM)
#NULL! - Incorrect range operator. Results from using a space instead of a comma or colon.
=SUM(A1 A10) → #NULL! (should be A1:A10)
You can test ISERROR against all these errors:
| Formula | Result | ISERROR Result |
|----------------------|---------|----------------|
| =10/0 | #DIV/0! | TRUE |
| =VLOOKUP("X",A1:B5,2,0) | #N/A | TRUE |
| ="text"+5 | #VALUE! | TRUE |
| =A1 | 100 | FALSE |
Combining ISERROR with IF for Custom Error Handling
ISERROR alone just tells you an error exists. The real power comes from combining it with IF to control what happens when errors occur:
=IF(ISERROR(A1/B1), "Cannot calculate", A1/B1)
This formula attempts to divide A1 by B1. If the division fails (B1 is zero or empty), it displays “Cannot calculate” instead of #DIV/0!. If successful, it shows the result.
Here’s a more sophisticated example for calculating profit margins:
=IF(ISERROR((B2-A2)/B2*100), "Invalid data", (B2-A2)/B2*100&"%")
This calculates profit margin as a percentage. If the calculation fails (maybe B2 is zero or contains text), you get a clean “Invalid data” message instead of an error code that confuses users.
For VLOOKUP operations, error handling prevents broken reports:
=IF(ISERROR(VLOOKUP(A2, Products!A:C, 3, FALSE)), "Product not found", VLOOKUP(A2, Products!A:C, 3, FALSE))
Notice the problem here? You’re executing the VLOOKUP twice—once to check for errors and once to get the result. This is inefficient and exactly why IFERROR was introduced.
ISERROR vs IFERROR: The Modern Approach
IFERROR, introduced in Excel 2007, consolidates error checking and handling into a single function:
=IFERROR(value, value_if_error)
Compare these approaches:
Old method with ISERROR:
=IF(ISERROR(VLOOKUP(A2, Products!A:C, 3, FALSE)), "Not found", VLOOKUP(A2, Products!A:C, 3, FALSE))
Modern method with IFERROR:
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")
The IFERROR version is shorter, clearer, and more efficient because it only evaluates the VLOOKUP once. Excel caches the result internally.
For division operations:
ISERROR approach:
=IF(ISERROR(A1/B1), 0, A1/B1)
IFERROR approach:
=IFERROR(A1/B1, 0)
So when should you use ISERROR? When you need to differentiate between error types or perform logic based on whether an error exists without necessarily replacing it. IFERROR is an all-or-nothing approach—it catches all errors the same way.
Real-World Example: Invoice Calculation System
Let’s build a practical invoice system that calculates totals with multiple potential failure points:
| A | B | C | D | E |
|------------|----------|------------|----------------|----------------------|
| Product | Quantity | Unit Price | Discount % | Line Total |
| Widget A | 10 | 25.50 | 10 | =IFERROR((B2*C2)*(1-D2/100),"Check data") |
| Widget B | 0 | 15.00 | #N/A | =IFERROR((B3*C3)*(1-D3/100),"Check data") |
| Widget C | 5 | text | 5 | =IFERROR((B4*C4)*(1-D4/100),"Check data") |
For Widget A, the formula calculates correctly: 10 × 25.50 × 0.90 = 229.50
For Widget B, the #N/A in the discount field would normally cascade an error through the calculation, but IFERROR catches it and displays “Check data”
For Widget C, the text in Unit Price creates a #VALUE! error, also caught and handled cleanly
Here’s a more complex example combining multiple error checks:
=IFERROR(
VLOOKUP(A2, PriceList!A:B, 2, FALSE) * B2 * (1 - IFERROR(VLOOKUP(A2, Discounts!A:B, 2, FALSE), 0)),
"Product not found"
)
This formula:
- Looks up the product price
- Multiplies by quantity
- Applies a discount (defaulting to 0% if no discount exists)
- Returns “Product not found” if the main price lookup fails
Best Practices and When to Use Specific Error Checkers
Use IFERROR as your default. It’s cleaner and performs better in most scenarios. Reserve ISERROR for situations requiring conditional logic:
=IF(ISERROR(A1), "This cell has an error", "This cell is valid: " & A1)
Use specific error checkers when you need granular control. If you only care about #N/A errors (common with lookups), use ISNA:
=IF(ISNA(VLOOKUP(A2, Products!A:C, 3, FALSE)), "Product not in system", VLOOKUP(A2, Products!A:C, 3, FALSE))
Or better yet, use IFNA (Excel 2013+):
=IFNA(VLOOKUP(A2, Products!A:C, 3, FALSE), "Product not in system")
Avoid excessive nesting. Each nested ISERROR check impacts calculation performance:
// Poor performance - multiple error checks
=IF(ISERROR(A1), 0, IF(ISERROR(B1), 0, IF(ISERROR(C1), 0, A1+B1+C1)))
// Better - single error check on the final result
=IFERROR(A1+B1+C1, 0)
Consider whether you need error handling at all. Sometimes errors are valuable feedback. If a #DIV/0! indicates a data quality problem, hiding it with IFERROR might mask issues that need fixing at the source.
Document your error handling. When you return custom messages or default values, add comments explaining why:
=IFERROR(Revenue/Customers, 0) // Returns 0 if no customers (avoids #DIV/0!)
ISERROR remains useful for building conditional logic around errors, but for straightforward error suppression and replacement, IFERROR and its siblings (IFNA, IFBLANK) provide superior solutions. Choose the right tool based on whether you need to detect errors or handle them—and remember that the best error handling often happens in data validation before formulas ever execute.