Part of the free Module 5: Excel Formulas and Functions · Function 43 of 102 · Full Excel course
The Excel ISERR function returns TRUE when a value is any error except #N/A, and FALSE otherwise. ISERR in Excel lets you react to genuine calculation faults such as #DIV/0!, #VALUE! or #REF! while leaving the “not found” result of a lookup untouched, which makes it more selective than ISERROR.
ISERR syntax
=ISERR(value)Arguments
| Argument | Required | Meaning |
|---|---|---|
| value | Required | The cell, expression or formula to test. |
ISERR returns TRUE for #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL! and the newer Excel 365 errors such as #SPILL! and #CALC!. It returns FALSE for #N/A and for every non-error value.
Step-by-step example
Cell A5 contains =1/0, which shows #DIV/0!.
=ISERR(A5)- Excel evaluates A5 and finds #DIV/0!.
- #DIV/0! is an error other than #N/A.
- ISERR returns TRUE. If A5 held a VLOOKUP that returned #N/A, the result would be FALSE.
Practical use cases
1. Message for real errors, keep #N/A visible
=IF(ISERR(A8), "Check formula", A8)Missing lookups still show #N/A, so users know the item is absent rather than that the sheet is broken.
2. Count calculation errors in a column
=SUMPRODUCT(--ISERR(E2:E500))3. Highlight faulty cells with conditional formatting
=ISERR(A2)4. Average while skipping error cells
=AVERAGE(IF(ISERR(B2:B100), "", B2:B100))Enter with Ctrl+Shift+Enter in Excel 2019 and earlier; Excel 365 handles it natively. AGGREGATE(1, 6, B2:B100) does the same with less typing.
5. Separate #N/A from other errors in an audit
=IF(ISNA(C2), "Not found", IF(ISERR(C2), "Formula error", "OK"))ISERR vs ISERROR vs ISNA
| Function | #N/A | Other errors | Normal value |
|---|---|---|---|
| ISERR | FALSE | TRUE | FALSE |
| ISERROR | TRUE | TRUE | FALSE |
| ISNA | TRUE | FALSE | FALSE |
Common mistakes and errors
- Expecting #N/A to be caught – by design ISERR ignores it. Use ISERROR or ISNA if #N/A matters.
- Using ISERR where IFERROR is enough – IF(ISERR(x), alt, x) evaluates x twice. IFERROR(x, alt) is shorter, but it also swallows #N/A; pick according to what you want hidden.
- Testing text that looks like an error – “#DIV/0!” typed as text is not an error; ISERR returns FALSE.
- Whole-range tests in legacy Excel – wrap in SUMPRODUCT or use array entry.
Tips and best practices
- Use ISERR when #N/A is expected and other errors are not, typically around lookups in data-entry sheets.
- Prefer IFERROR or IFNA for replacement; use ISERR when you need a TRUE/FALSE for counting, formatting or validation.
- Count error cells per column with SUMPRODUCT(–ISERR(range)) as a quality check before publishing a report.
- Use AGGREGATE with option 6 to sum or average while ignoring errors instead of array-entering IF(ISERR()).
- Remember the three-way split: ISNA for #N/A, ISERR for everything else, ISERROR for all.
Related functions
- ISERROR – TRUE for every error including #N/A.
- ISNA – TRUE only for #N/A.
- IFERROR and IFNA – replace errors in one step.
- ERROR.TYPE – identifies which error occurred.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Does ISERR treat #N/A as an error?
No. ISERR returns FALSE for #N/A and TRUE for every other error type. That is the only difference between ISERR and ISERROR.
Can ISERR check many cells at once?
Yes. SUMPRODUCT(–ISERR(range)) counts the error cells, and in Excel 365 ISERR(range) spills an array of TRUE/FALSE values.
When should I use ISERR instead of ISERROR?
Use ISERR around lookups when #N/A is an expected “not found” result that you want to keep, but genuine faults such as #REF! or #DIV/0! should be flagged or replaced.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.