Skip to content

Excel function · Information

Excel ISERROR function

ISERROR returns TRUE when the value is any error, such as #DIV/0!, #N/A, #VALUE! or #REF!, and FALSE otherwise. Use it to flag faulty rows in a check column and to return a different result when there is an error.

In Turkish Excel: EHATALIYSA

What it does

ISERROR does not tell you the result of the calculation, only whether it is an error: =ISERROR(B2/C2) returns TRUE when C2 is zero. That lets you flag faulty rows in a check column, filter them or count them.

In older versions, the pattern =IF(ISERROR(B2/C2),0,B2/C2) was used to show another value instead of an error; it is long because the formula is written twice. Today IFERROR does the same in one go: =IFERROR(B2/C2,0).

To test only for #N/A use ISNA; to test for every error except #N/A use ISERR. When different error types need different handling, these are more selective than ISERROR.

Syntax

English Excel=ISERROR(value)
Turkish Excel=EHATALIYSA(değer)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
valueRequiredThe value or formula to test, e.g. B2/C2 or MATCH(A2,$D$2:$D$5,0).

Examples

1Flag rows where the calculation fails

Unit cost is total cost divided by quantity. The error arises inside the formula: the eraser's quantity is 0 and the stapler's was typed as words. Column D tests for the error; column E shows a warning on faulty rows and the cost on the others.

D2fx=ISERROR(B2/C2)
Flag rows where the calculation fails: example table
ABCDE
1ItemTotal CostQtyError?Unit Cost
2Notebook4,800.00120FALSE40.00
3Eraser1,250.000TRUECheck
4Binder3,150.0090FALSE35.00
5Stapler2,640.00forty-eightTRUECheck
  • D2 (filled down to D5)=ISERROR(B2/C2)
  • E2 (filled down to E5)=IF(ISERROR(B2/C2),"Check",B2/C2)

The eraser's division gives #DIV/0! and the stapler's #VALUE!; ISERROR returns TRUE for both. Column E shows Check on those rows and the cost on the others: 40.00 for the notebook.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Find order codes missing from the price list

When MATCH cannot find a code in the price list (column D) it returns #N/A; ISERROR turns that into TRUE.

B2fx=ISERROR(MATCH(A2,$D$2:$D$5,0))
Find order codes missing from the price list: example table
ABCD
1Order CodeNot in ListPrice List
2KR-110FALSEKR-110
3KR-135TRUEKR-120
4KR-120FALSEKR-130
5KR-190TRUEKR-150
  • B2 (filled down to B5)=ISERROR(MATCH(A2,$D$2:$D$5,0))

KR-135 and KR-190 are not in the price list, so they give TRUE; KR-110, which is listed, gives FALSE. ISNA is more selective for this job: it counts only the not-found error.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Always FALSE

Cause: The inner formula already catches the error with IFERROR, so the value reaching ISERROR is no longer an error.

Fix: Apply the test to the inner formula without IFERROR.

Error types lumped together

Cause: ISERROR treats every error the same; a #NAME? from a typo returns TRUE just like a code that is not found.

Fix: Use ISNA for lookups that find nothing and look into other errors separately.

Good to know

  • To count the error results in a column: =SUMPRODUCT(--ISERROR(D2:D9)).
  • To put a word in the check column instead of TRUE/FALSE, wrap it in IF: =IF(ISERROR(B2/C2),"Check","").

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Check column to my unit cost table that says "Check" on rows where the calculation returns an error

Convert to table

Bring your table into the tool and type this. Nexell adds the Check column with real Excel formulas and the preview shows which rows were flagged; in the downloaded file, the flag clears by itself once you fix the data.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between ISERROR and IFERROR?

ISERROR only returns TRUE or FALSE; you decide with IF what to show instead of the error. IFERROR tests and replaces the error with the value you give in one step; in most cases it is shorter.

Related functions

All information functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free