How to Use Excel's ISERROR Function

by Contributor

One of Microsoft Excel's most useful features is its ability to test the integrity of data entered into spreadsheets. If a spreadsheet has thousands or even millions of pieces of information, it would be very time-consuming to check each figure manually for errors. Luckily, Excel has several function formulas to speed this process. One such function is the ISERROR formula.

Know what Excel's ISERROR function does. The ISERROR formula checks whether a value entered is an error. Errors can include #N/A, #VALUE, #REF, #DIV/0, #NUM, #NAME or #NULL. If ISERROR detects an error, it returns a value of "TRUE." If there are no errors of these types, ISERROR returns a value of "FALSE."

Understand the different types of error messages that ISERROR checks. #N/A means that someone didn't enter required information. Some spreadsheets have mandatory fields that are important to a document's validity, so finding values of #N/A helps the spreadsheet author track down the necessary missing information. #VALUE indicates a user input the wrong type of data necessary for a specific cell. Perhaps a cell requires that you type "M" or "F" for gender and you enter "2." This results in a #VALUE error. #REF errors indicate you've used formulas that link to cells, values or programs that no longer exist.

Continue to differentiate between error types. #DIV/0 is just as it sounds. You attempted to divide a number by zero, whether manually or as part of a formula. #NUM appears when you enter a nonnumeric value in a formula that requires numbers. It can also appear if the result of a formula is too large or small for Excel to represent. #NAME appears when you misspell a formula command, omit quotation marks around strings of text or omit a colon in a range reference. #NULL appears when you incorrectly use a colon instead of a comma (and vice versa) when representing range operators or when ranges don't intersect.

Use Excel's ISERROR function. The ISERROR function generally refers to cell references, not hard numbers. Say you want to find out if the range of cells A1:A14000 contains an error. Click your mouse on a blank cell and type the following formula: =ISERROR(A1:A14000). Don't put a period at the end of the function formula. Press "Enter." If Excel finds an error in this range of cells, it displays "TRUE." If not, the cell displays "FALSE."