Part of the free Module 5: Excel Formulas and Functions · Function 45 of 105 · Full Excel course
The Excel ISERROR function returns TRUE when a value is any error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!) and FALSE for every normal value. ISERROR in Excel is the classic building block for error handling: pair it with IF to replace errors, with SUMPRODUCT to count them, or with conditional formatting to highlight them.
ISERROR syntax
=ISERROR(value)Arguments
| Argument | Required | Meaning |
|---|---|---|
| value | Required | The cell, expression or formula to test for an error. |
ISERROR is available in every version of Excel, which is why IF(ISERROR(…)) is still common in workbooks that predate IFERROR (Excel 2007).
Step-by-step example
Cell A1 contains =10/0 and displays #DIV/0!.
=ISERROR(A1)- Excel evaluates A1 and finds an error value.
- Any error type qualifies.
- ISERROR returns TRUE. With 10 in A1 it returns FALSE; with #N/A in A1 it returns TRUE.
Practical use cases
1. Replace any error with a message
=IF(ISERROR(D4), "Error detected", D4)In Excel 2007 and later, IFERROR(D4, “Error detected”) gives the same result without evaluating D4 twice.
2. Count errors in a range
=SUMPRODUCT(--ISERROR(E1:E10))3. Sum while ignoring error cells
=SUMPRODUCT(IF(ISERROR(B2:B100), 0, B2:B100))In Excel 365 this works directly; older versions need Ctrl+Shift+Enter. AGGREGATE(9, 6, B2:B100) is a non-array alternative.
4. Highlight every error on a sheet
=ISERROR(A1)Apply as a conditional-formatting rule to the used range.
5. Test whether text contains a word
=NOT(ISERROR(SEARCH("urgent", A2)))SEARCH returns #VALUE! when the word is missing; ISERROR converts that into a clean TRUE/FALSE.
6. Data-validation rule that rejects errors
=NOT(ISERROR(VALUE(A2)))ISERROR vs ISERR vs ISNA
| Function | #N/A | Other errors | Normal value |
|---|---|---|---|
| ISERROR | TRUE | TRUE | FALSE |
| ISERR | FALSE | TRUE | FALSE |
| ISNA | TRUE | FALSE | FALSE |
Common mistakes and errors
- Hiding real problems – wrapping everything in IF(ISERROR()) masks typos and broken references along with harmless #N/A results. Prefer ISNA or IFNA around lookups.
- Double evaluation – IF(ISERROR(x), alt, x) calculates x twice, slowing big sheets. Use IFERROR where available.
- Testing text that looks like an error – a cell containing the text “#N/A” is not an error; ISERROR returns FALSE.
- Legacy array behaviour – ISERROR(range) in a single cell of Excel 2019 or earlier tests only one cell unless wrapped in SUMPRODUCT.
- Circular logic – ISERROR cannot test the cell it lives in; that creates a circular reference.
Tips and best practices
- Use IFERROR for replacement and ISERROR for detection (counting, conditional formatting, validation).
- Narrow the test to ISNA or ISERR whenever only one kind of error is expected; broad traps hide bugs.
- Audit with Go To Special > Formulas > Errors (F5) to jump to every error cell on a sheet.
- Use AGGREGATE(function, 6, range) to sum, average or find MAX while skipping errors without array formulas.
- Combine with SEARCH or FIND for contains-text tests, or use ISNUMBER(SEARCH()) which reads more naturally.
Related functions
- ISERR – ignores #N/A.
- ISNA – detects only #N/A.
- IFERROR – test and replace in one step.
- ERROR.TYPE – returns a code identifying the error.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Does ISERROR recognise #N/A as an error?
Yes. ISERROR returns TRUE for all error types including #N/A. Use ISERR if you want #N/A excluded, or ISNA if you want only #N/A.
What is the difference between ISERROR and IFERROR?
ISERROR returns TRUE or FALSE and needs an IF around it to replace the error. IFERROR does both steps in one function and evaluates the formula only once, so it is shorter and faster in Excel 2007 and later.
Can ISERROR check multiple cells at once?
Yes, inside SUMPRODUCT or another array-aware function: SUMPRODUCT(–ISERROR(A2:A100)) counts the errors. Excel 365 spills ISERROR(range) as an array automatically.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.