ISERROR function
Quick answer
ISERROR returns TRUE for any error value, such as #N/A, #VALUE!, #REF!, #DIV/0! or #NAME?.
- Syntax
=ISERROR(value)- Most common use
=IF(ISERROR(B3/C3),"No visits",B3/C3)returns No visits (Zero visits would give #DIV/0!, so the text shows instead)
Use it to count or flag rows where a calculation failed. When you just want a fallback value, IFERROR does the same job in fewer keystrokes.
- Available in
- Every current version
- Category
- Information functions
- Returns
- TRUE if the value is any error, FALSE otherwise.
- Google Sheets
- Available in Google Sheets with the same argument.
Syntax
=ISERROR(value)
| Argument | Required? | What it is |
|---|---|---|
value | Required | The value, cell or formula to test. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =IF(ISERROR(B3/C3),"No visits",B3/C3) | No visits | Zero visits would give #DIV/0!, so the text shows instead |
| F2 | =SUMPRODUCT(--ISERROR(B2:B5/C2:C5)) | 2 | Two rows fail: one divides by zero, one divides by text |
| G2 | =IF(ISERROR(MATCH("Uptown",A2:A5,0)),"Not a store","Listed") | Not a store | MATCH returns #N/A for a missing name |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Store | Sales | Visits | Airport per visit | Rows that fail | Uptown listed? | |
| Downtown | 4200 | 350 | No visits | 2 | Not a store | |
| Airport | 1800 | 0 | ||||
| Mall | 3100 | n/a | ||||
| Harbor | 2650 | 290 |
Practice ISERROR
Graded instantly, then re-checked on changed data.
Price per unit, safely
D2, use ISERROR to return the price per unit (B2 divided by C2), or 0 if the division fails. Fill down to D7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| It hides problems you needed to see | ISERROR is TRUE for every error, including #REF! from a deleted column and #NAME? from a typo. Use ISNA when you only mean "not found". |
| The formula is written twice | IF(ISERROR(x),"",x) calculates x twice. IFERROR(x,"") gives the same result in one step. |
Tips
- Highlight broken rows with conditional formatting: =ISERROR($D2).
Questions
What is the difference between ISERROR and IFERROR?
ISERROR only tests and returns TRUE or FALSE. IFERROR returns the value, or a fallback if it's an error, so it's shorter for the common case.
What is the difference between ISERROR, ISERR and ISNA?
ISERROR catches every error. ISERR catches every error except #N/A. ISNA catches only #N/A.
How do I count errors in a range?
=SUMPRODUCT(--ISERROR(D2:D50)) counts every cell in D2:D50 that holds an error.
Answers that use ISERROR
In other languages
ISERROR is ESERROR in Spanish Excel and ISTFEHLER in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .