How to Handle Errors in Excel Formulas


I’m sure that this is everyone’s favorite subject – error handling in Excel formulas. The errors that typically show up on a spreadsheet are #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!. There are a variety of built-in functions that Excel uses to handle errors. They do similar functions but have a few varieties and differences.

Some of the built-in formula error functions are: ISERR, ISERROR, ISNA, IFERROR, and IFNA. There is a difference between the “IS” and “IF” functions. The “IS” functions check if a value is an error and then return either a TRUE or FALSE. The “IF” functions return a value that you specify if there is an error, or the result of the formula if there is no error.  The variety of functions allow flexibility and creativity on how you decide to handle errors in your spreadsheets.

First, let’s consider the “IS” error functions.

“IS” Error Functions in Excel Formulas

ISERR

ISERR(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISERR will check if a value is an error and returns TRUE or FALSE. It checks these errors: #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Therefore, note that it checks for every error, except for the #N/A error.

ISERROR

ISERROR(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISERROR will check if a value is an error and returns TRUE or FALSE. It checks these errors: #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Therefore, note that it checks for every error, including the #N/A error.

ISNA

ISNA(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISNA will check if a value is an error and returns TRUE or FALSE. It only checks for the #N/A error.

“IF” Error Functions in Excel Formulas

IFERROR

IFERROR(value, value_if_error)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula
    • value_if_error = the value that you want to return if there is an error. This is a required value

IFERROR will check if a value is an error and returns a specified value or formula if there is an error. If there is no error, it will return the result of the cell or formula being checked. It checks for these errors: #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Note that it checks for every error, including the #N/A error.

IFNA

IFNA(value, value_if_na)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula
    • value_if_na = the value that you want to return if there is an #N/A error. This is a required value

IFNA will only check if a value is an #N/A error and returns a specified value or formula if it is. If there is no #N/A error, it will return the result of the cell or formula being checked. It checks only for the #N/A error.

Why the Difference in Functions for Excel Formulas?

There are basically two differences in the functions. The first is the difference between the “IS” and ”IF” types of functions. The second is the #N/A difference.

The “IS” and “IF” Error Functions Difference

The “IS” functions return a TRUE or FALSE. This can be used for logical evaluations and actions within a formula in the same cell or within other other cells.

The “IF” functions can be used to change what is displayed in the current cell if there is an error.

While both functions can be used to change what is displayed in a cell, the “IF” function is shorter and less complicated to read. If the “IS” function is used, it needs to be combined with an IF function to produce the same result as using the “IF” function by itself.

For example, which of the following two error handling formulas do you find easier to read and understand? Which one requires less calculation steps?

ISERROR FORMULA IFERROR FORMULA
=IF(ISERROR(D1/C1),B1,D1/C1) =IFERROR(D1/C1,B1)

Both Excel formulas yield the same results. The ISERROR function, though, needs to be wrapped within an IF function to perform the same action as the IFERROR formula. This creates a longer formula, and, don’t quote me on this, but I’m sure that it requires Excel to do an extra calculation step in the cell compared to the IFERROR formula. That may be fine for a few error trapping formulas, but imagine multiplying that by thousands or tens of thousands of cells on a large spreadsheet.

Not only that, but with the shorter IFERROR formula, it is easier to follow the flow of the formula.Imagine if, a few months down the road, you are trying to decipher what  your formula is doing, or someone else who isn’t familiar with it is trying to figure out your formulas. The simpler, more direct formula would certainly be easier to understand.

Notice in the two images below the two different formulas, and that they both have the same result.

Excel Formulas

Formula View

Excel Formulas

Result View

The #N/A Difference

Both the “IS” and “IF” functions allow you to handle #N/A errors in your Excel formulas differently. Both types of functions allow you to specifically check for only the #N/A error by itself, or to group it with all of the errors that you can come across in Excel.

However, the “IS” functions has the unique ISERR function that checks for all errors, except for #N/A. The “IF” functions do not have this unique ability.

So if you need to check for all errors except the #N/A error, you will need to use the ISERR function and perhaps wrap it within an IF function.

Conclusion

Hopefully, I have shown, and explained to you, the variety of ways to handle and trap errors in your Excel formulas.

Depending on what you need in the types of errors you want to trap, Excel has a way to handle the error.

If you have any questions, or comments on ways that you handle Excel errors, please comment below.

Leave a Reply