ISERROR in Excel: How It Works and When to Use It

How-to basics · Updated November 18, 2026

In Excel, ISERROR checks whether a value or formula result is any Excel error and returns TRUE or FALSE. If the value to check is in A2, enter =ISERROR(A2); the result is TRUE if A2 contains an error and FALSE otherwise.

What ISERROR checks

ISERROR is an information function: it identifies the error state of its argument rather than fixing the problem or explaining its cause. It can detect common errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!, as well as errors produced by newer Excel features. The exact error displayed depends on the calculation or feature that produced it.

The function returns a logical value, not the error itself. If A2 contains the number 12, text such as “pending,” a blank cell, or a formula that successfully returns a result, ISERROR returns FALSE. If A2 contains a formula whose calculation produces an error, ISERROR returns TRUE. A text string that merely looks like an error—for example, the characters “#N/A” entered as ordinary text—is not an Excel error and returns FALSE.

This distinction makes ISERROR useful for testing results, but important to keep in mind when cleaning imported data: an error value and text that resembles one are different things to Excel.

Syntax and how the function works

ISERROR has one argument, called value. That argument can be a cell reference, a formula, or another expression Excel can evaluate. The general syntax is ISERROR(value); in a worksheet formula, the equals sign goes at the beginning, as in the example in the opening paragraph.

Excel evaluates the argument first, then checks its result. For example, if a calculation divides 10 by zero, the calculation produces #DIV/0!; ISERROR applied to that calculation returns TRUE. It does not replace the error inside the original calculation. Its output is simply a Boolean value that another formula, a filter, or conditional formatting can use.

If the referenced cell is empty, ISERROR returns FALSE because a blank is not an error. A formula that returns an empty string is also not an error. These cases can matter when a report treats blanks as missing information: ISERROR alone will not identify them as missing.

Use ISERROR with IF to display a message

A common reason to use ISERROR is to make a formula respond differently when a referenced result is an error. Suppose A2 contains a lookup result or another calculation. To show a message when A2 has an error and otherwise show its value, enter this formula in B2:

With the value or formula result to check in A2, enter in B2: =IF(ISERROR(A2),"Check input",A2). If A2 contains an error, B2 displays “Check input”; if A2 contains 42, B2 displays 42.

The IF function tests the TRUE/FALSE result from ISERROR. This arrangement lets you choose a specific response, such as “Review source,” “No match,” or a blank result. It is especially useful when different errors should prompt different messages or when you want to leave successful results unchanged.

For a simple status flag, with the result to test in A2, enter this in B2: =IF(ISERROR(A2),"Error found","No error"). If A2 contains =1/0, B2 displays “Error found.” If A2 contains 5, it displays “No error.” The formula labels every error the same way, so it is a broad check rather than a diagnosis.

Practical examples for worksheets

ISERROR can help in several everyday spreadsheet situations. The best formula depends on whether you want to identify an error, replace it, or keep a visible record of what went wrong.

  • Check a calculation: If A2 contains a potentially problematic calculation, a neighboring cell can return TRUE or FALSE so errors are easy to filter or count.
  • Guard a division: With the numerator in A2 and denominator in B2, put this in C2: =IF(ISERROR(A2/B2),"Cannot calculate",A2/B2). If B2 is zero, C2 shows “Cannot calculate”; if A2 is 20 and B2 is 4, it shows 5.
  • Flag lookup failures: If A2 contains a lookup formula that may return an error, test A2 in a separate status column. This distinguishes failed results from successful matches without changing the lookup itself.
  • Count error cells: With results in A2:A20, use =SUMPRODUCT(--ISERROR(A2:A20)) in another cell. It returns the number of cells in that range whose values are errors. For example, if three cells contain errors, the count is 3.

For the division example, testing the same calculation twice means Excel may evaluate it twice. That is usually fine for a small formula, but a complicated expression may be easier to maintain if it is calculated in a helper cell first. In Microsoft 365, LET can also name a calculation inside a formula, but a helper column is often clearer for people who need to audit the workbook.

ISERROR compared with IFERROR and IFNA

ISERROR, IFERROR, and IFNA all relate to errors, but they serve different purposes. ISERROR reports whether its argument is an error. IFERROR returns a chosen fallback value when its first argument is an error; otherwise, it returns the original result. IFNA behaves similarly but catches only the #N/A error.

With a calculation in A2, enter this in B2 to replace any error with a readable message: =IFERROR(A2,"Check calculation"). If A2 contains 8, B2 returns 8; if A2 contains an error, B2 displays “Check calculation.” This is more concise than nesting IF and ISERROR when the only goal is to substitute a fallback.

With a lookup result in A2, enter this in B2 if only a missing-match error should be handled: =IFNA(A2,"No match"). IFNA leaves other errors visible, which can help reveal a broken reference or invalid calculation rather than disguising it as a normal missing match.

  • Choose ISERROR when you need a TRUE/FALSE test or want to make different decisions based on the test.
  • Choose IFERROR when any error should be replaced with a specified result.
  • Choose IFNA when only #N/A should be replaced and other errors should remain visible.

A common misconception is that IFERROR is always safer because it makes a worksheet look cleaner. Hiding every error can conceal a wrong range, deleted reference, or invalid calculation. Use a fallback that communicates the situation, and keep errors visible where they are useful for troubleshooting.

Common problems and how to troubleshoot

If ISERROR returns FALSE when you expected TRUE, first check whether the cell contains a real Excel error or merely text that looks like one. Imported reports sometimes store strings such as “#VALUE!” as text. Clicking the cell and inspecting the formula bar, or testing the cell’s behavior in a simple calculation, can help distinguish the two.

Also check what the formula is actually testing. A formula may refer to the wrong cell, or a calculation that once produced an error may now return a valid result. ISERROR reports the current evaluated result, not whether a cell has ever had an error.

If a formula with IF and ISERROR still displays an unexpected result, check the IF branches. The value returned when the test is FALSE may itself contain a different error, and ISERROR only determines which branch IF selects; it does not repair that branch. For a lookup formula, for example, a fallback can hide “not found” while a separate reference problem still needs attention.

Remember that blank cells and empty strings are not errors. If the real requirement is to detect either an error or a blank, test for both conditions explicitly. Likewise, a zero is a valid number, not an error; a division formula may produce an error only when that zero is used as a denominator.

Use the result in conditional formatting

You can use a TRUE/FALSE test to visually flag error cells without changing their values. Select the range you want to format, then choose Home > Conditional Formatting > New Rule. Choose “Use a formula to determine which cells to format,” enter a formula that checks the first cell in the selected range, select a format, and confirm. For a range starting at A2, use =ISERROR(A2). Excel adjusts the relative reference for other cells in the selected range.

For example, selecting A2:A20 before creating the rule means the test starts with A2 and is evaluated for each cell in that range. Choose a fill color or font style that makes errors easy to spot. This formatting does not change an error into a value, so formulas that depend on the cells will still encounter the underlying errors.

On Excel for Mac, the Conditional Formatting commands are also on the Home tab, although some dialog layouts can differ. In Excel for the web, the available rule options and dialog presentation may vary; if the formula-based rule is not available in the interface you are using, open the workbook in the desktop application.

Compatibility and formula behavior

ISERROR is a long-standing Excel function and is available in Microsoft 365 for Windows as well as current Mac and web versions. It does not require a newer function set such as XLOOKUP or dynamic array formulas. The function name and basic use are the same across these platforms, though menus and formatting dialogs can differ.

In Microsoft 365, a formula can sometimes test a range and return multiple TRUE/FALSE results that spill into neighboring cells. For instance, if A2:A5 contains individual values or errors and the cells below the formula are clear, entering =ISERROR(A2:A5) in B2 returns one test result for each corresponding cell. If the spill area is blocked, Excel may display a spill error. In older Excel versions, array behavior may require different entry methods, so a copied-down formula that tests one cell at a time is a dependable option.

When sharing a workbook with someone using an older version, basic ISERROR formulas are generally more portable than formulas built around newer functions. Still, a workbook’s compatibility can depend on other functions in the same formula, not just ISERROR.

Frequently Asked Questions

Does ISERROR detect #N/A?
Yes. ISERROR returns TRUE for #N/A and for other Excel error values. If you want to catch only #N/A, use IFNA for a replacement or ISNA for a TRUE/FALSE test.

What is the difference between ISERROR and ISERR?
ISERROR returns TRUE for all error values, including #N/A. ISERR checks for errors other than #N/A, so it returns FALSE for that particular error.

Does ISERROR hide an error?
No. ISERROR only returns TRUE or FALSE. To display a replacement value instead, use IFERROR, or use IF with ISERROR when you need to choose your own conditional behavior.

Will ISERROR identify a blank cell?
No. A blank cell is not an error, so ISERROR returns FALSE. Test for blanks separately if your worksheet needs to treat missing entries as a problem.

Can ISERROR check a formula directly?
Yes. Its argument can be a calculation rather than a cell reference. For example, you can test whether a division calculation produces an error; if you reuse a complex calculation in several places, consider a helper cell to make the worksheet easier to inspect.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.