Knowledge base / Excel

ISERROR in Excel: error flags and data checks

I use simple division to distinguish error detection from result replacement, including text that only looks like an error and incomplete reporting totals.

Kajo RudzińskiPublished Updated 6 min read

A scanner diverts a damaged block into a separate inspection tray

ISERROR checks whether a value is an Excel error and returns TRUE or FALSE. It does not repair source data or replace a failed result with another number by itself. I mainly use it to flag records that need investigation.

The Polish equivalent is CZY.BŁĄD. I will distinguish detecting an error, handling an expected missing value and concealing a problem in a report. These synthetic examples use simple division and logical results.

Syntax and the first example

The Microsoft documentation gives the syntax:

=ISERROR(value)

The argument can be a cell or an expression. =ISERROR(10/0) returns TRUE because division by zero produces #DIV/0!. =ISERROR(10/2) returns FALSE. The corresponding Polish formulas are =CZY.BŁĄD(10/0) and =CZY.BŁĄD(10/2).

Pay attention to what the response means. TRUE identifies an Excel error, while FALSE only says that the supplied value is not an error. A negative price, an incorrect category or an accidental zero can still pass this check.

Keep a separate control column

Create headings Numerator, Denominator, Result and Error flag in A1:D1. Enter the three synthetic pairs below, put =A2/B2 in C2 and copy it through C4. Put =ISERROR(C2) in D2 and copy that down too.

A B C: result D: error
10 2 5 FALSE
8 0 #DIV/0! TRUE
0 4 0 FALSE

The zero in the last row is a valid result. The error in the second row comes from a zero denominator. I do not want to turn both situations into the same numeric zero, because they carry different information.

A control column lets you filter TRUE values and investigate without destroying the original results. You can identify the record and trace its denominator back to the source. That is often more useful than replacing every error with an empty cell.

Actual errors differ from error-looking text

Enter #DIV/0! in a separate cell as text by using a leading apostrophe. ISERROR returns FALSE for that string. The cell contains characters rather than the result of a failed calculation.

This matters with imports. A system can export a status such as “ERROR” or a literal string resembling an Excel error code. ISERROR does not search for error-related words. Textual statuses need their own validation rule.

Likewise, a valid number is not proof of business accuracy. If somebody enters revenue of 999 instead of 99, ISERROR returns FALSE. Detecting that mistake requires range rules, comparison with the source or control totals.

A scanner separates a damaged block from valid values

ISERROR, ISERR and ISNA

All three functions return logical values, but they cover different cases. Their Polish names are CZY.BŁĄD, CZY.BŁ and CZY.BRAK. The old Polish name “CZY.BŁĄD.NA” is not a valid function name.

All three return FALSE for an ordinary number. For two example errors:

Function #DIV/0! #N/A
ISERROR TRUE TRUE
ISERR TRUE FALSE
ISNA FALSE TRUE

ISERROR detects errors, ISERR detects errors other than #N/A, and ISNA checks specifically for #N/A. When checking a column’s general calculation quality, I may want every error. When investigating a missing dictionary match, a narrower condition is often appropriate.

Choose the function according to the kind of problem you can actually handle. A broad error flag is useful for review, while a specific flag can support a particular expected case. Neither one establishes why the error arose.

IFERROR replaces a result

If I want an alternative value for a failed expression, I use IFERROR. For our columns:

=IFERROR(A2/B2,"Check data")

Ten divided by two still returns 5. Eight divided by zero returns “Check data.” The Polish equivalent is =JEŻELI.BŁĄD(A2/B2;"Sprawdź dane").

You may also encounter the older IF-with-ISERROR pattern, which repeats the expression in two places. IFERROR states the replacement intention more compactly. ISERROR alone remains useful when the desired result is a logical flag rather than a replacement value.

A message does not remove the cause of a failure. If the result feeds another aggregation, text can be ignored by some operations. I therefore treat quality checks and reader-facing messages as part of the report design. Changing an error into an empty rectangle does not establish that the report is complete.

Handle missing matches narrowly

For VLOOKUP, an absent dictionary code may be an expected condition. IFNA handles #N/A while keeping other errors visible.

Assume a code in E2 and a price list in A2:B4, with codes in its first column and prices in its second:

=IFNA(VLOOKUP(E2,$A$2:$B$4,2,FALSE),"Missing code")

This assumes that #N/A in the process really represents a missing match. If a matched source price already contains #N/A, dictionary checks need to distinguish that situation. Handling an error code does not diagnose its origin.

In newer Excel versions, you can use the missing-match argument of XLOOKUP. Replacing every error with “Missing code” would be misleading when the actual cause is a broken reference or an unsuitable formula.

Conditional formatting without changing values

To highlight problems, use ISERROR in a conditional formatting rule. For C2:C4, choose a formula-based rule and enter:

=ISERROR($C2)

Column C is fixed, while the row changes for each evaluated cell. Select a visible fill and inspect whether the second record is highlighted. The rule points to the error without altering the original calculation.

If you select a wider range covering whole records, the same condition can highlight rows according to errors in C. The formula’s row number must correspond to the first selected row. An offset rule can otherwise highlight the neighboring record.

What this check cannot repair

ISERROR evaluates the value supplied to a calculation. It cannot repair formula syntax that Excel refuses to accept while you enter it. It also cannot establish whether a missing lookup came from an unknown code, a data-type mismatch or an extra space.

Newer Excel versions include array-related errors such as #SPILL!. When results have no room to spill, inspect the output area and blocking cells. Hiding a message does not create free cells. The dynamic array documentation explains the placement rules.

I do not automatically sum “everything except errors” without deciding what the omitted records mean. If prices are missing for some transactions, the remaining values may be addable while still failing to represent total sales. The report needs to disclose its incompleteness.

Exercise: detect, explain and decide

Return to the three number pairs. Inspect the ISERROR results, then change the second denominator from zero to 4. Its division result should become 2 and the flag should become FALSE.

Add error-looking text and a separate missing-data error. Compare ISERROR, ISERR and ISNA. Describe which conditions should only be highlighted, which can receive an alternative message and which require the report to stop for investigation. The ISTEXT entry can help distinguish value types.

Key takeaways

Does ISERROR repair a formula?

No. It returns TRUE when the supplied value is an error, and FALSE otherwise.

How does ISNA differ from ISERROR?

ISNA detects only #N/A, while ISERROR covers errors more broadly. ISERR detects errors other than #N/A.

Will error-looking text be detected?

No. A string resembling an error code remains text. The function detects Excel error values.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.