How to use the Excel ISERROR function

Summary

The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when found.

Purpose

Test for any error

Return value

A logical value (TRUE or FALSE)

Syntax

=ISERROR (value)

Arguments

value - The value to check for any error.

Usage notes

Use the ISERROR function to see if a cell contains any error message, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

For example, =ISERROR(A1) will return TRUE if A1 is is displaying an one of the errors mentioned above , and FALSE if not.

Frequently, value is supplied as a cell address, but you can use it to trap errors inside more complex formulas as well.

ISERROR formula examples

Match first error

If you need to match the first error in a range of cells, you can use an array formula based on the MATCH and ISERROR functions. In the example shown, the formula is: {=MATCH(TRUE,ISERROR(B4:B11),0)} This is an array...

How to fix the #NAME? error

The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a...

How to fix the #NUM! error

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas...

Count cells that do not contain errors

To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the generic form of the formula (above) rng represents the range in which you'd like to...

Count cells that contain errors

To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula: =SUMPRODUCT(--ISERROR(B5:B9)) How this formula works...

Get first word

If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. From the example, the formula looks like this: =LEFT(B4,FIND(" ",B4)-1) How this formula...

How to fix the #DIV/0! error

About the #DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or...

How to fix the #VALUE! error

The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel...

Lookup last file revision

To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX. In the example shown, the formula in cell H6 is: {=MAX(IF(...

How to fix the #NULL! error

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "...

Related videos

How to trap errors in formulas

Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use. In this video, we'll look at some ways to trap errors in a formula.

Related functions

Excel ISERR Function

The Excel ISERR function returns TRUE for any error type except the #N/A error. You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found.

Excel ERROR.TYPE Function

The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See below for a key to the error codes returned...

Excel ISBLANK Function

The Excel ISBLANK function returns TRUE when a cell contains is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.

Excel ISEVEN Function

The Excel ISEVEN function returns TRUE when a numeric value is even, and FALSE for odd numbers. ISEVEN will return the #VALUE error when a value is not numeric.

Excel ISFORMULA Function

The Excel ISFORMULA function returns TRUE when a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.

Excel ISLOGICAL Function

The Excel ISLOGICAL function returns TRUE when a cell contains the logical values TRUE or FALSE, and returns FALSE for cells that contain any other value, including empty cells.

Excel ISNA Function

The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for an error and display a friendly message when it appears...

Excel ISNONTEXT Function

The Excel ISNONTEXT function returns TRUE for any non-text value, for example, a number, a date, a time, etc. The ISNONTEXT function also returns TRUE for blank cells, and for cells with formulas that return non-text results.

Excel ISNUMBER Function

The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.

Excel ISODD Function

The Excel ISODD function returns TRUE when a numeric value is odd, and FALSE for even numbers. ISODD will return the #VALUE error when a value is not numeric.

Excel ISREF Function

The Excel ISREF returns TRUE when a cell contains a reference and FALSE if not. You can use the ISREF function to check if a cell contains a valid reference.

Excel ISTEXT Function

The Excel ISTEXT function returns TRUE when a cell contains a text, and FALSE if not. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.

Good links

Microsoft ISERROR function documentation

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.