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.
Version
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
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...
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...
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...
About spilling and the #SPILL! error
With the introduction of Dynamic Arrays in Excel, formulas that return multiple values "spill" these values directly onto the worksheet. The rectangle that encloses the values is...
Working from the inside out, the ISERRROR function returns TRUE when a value is a recognized error, and FALSE if not.
When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE...
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 "...
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...
At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.
To find...
The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the...
In this example, the goal is to count the number of cells in a range that do not contain errors.
Working from the inside out, we first use the ISERROR function on the entire range:
ISERROR(B5:B14) // check all 10...
FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars.
The LEFT function then extracts characters...
Related videos
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
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
