Why You Should Be Cautious About Using Excel’s IFERROR Formula

The IFERROR formula was designed to solve a common aesthetic problem that most of us have encountered when using Excel – when we know that there are errors in our data, but we’d prefer not to see Excel’s standard error message notation.  Error messages usually consist of all caps lettering that is preceded with the “#” symbol, and some of them have an exclamation point or question mark at the end.  Visually, Excel’s error messaging is intended to be “jarring” because Excel’s developers want you to alert you that something might be wrong with your file.  But if the error is expected or benign, such a loud visual indicator may not be necessary.

Enter the IFERROR formula.  IFERROR has become very popular because it allows you to mask Excel’s error message with a blank space, or a custom message of your own, such as “Not Found“.  For example, if I were to run a VLOOKUP formula on several numbers, and I knew one of the values was not going to be found within my data set, there are situations where I’d much rather see the phrase “Not Found” than the “#N/A” message.  This is especially true if I need to present this output in a more formal medium, such as a PowerPoint deck.

In the example below, imagine I was pulling rates by city from a larger data set, but I knew that Detroit and Mexico City would not be found.  The table on the right shows how you can improve the visual design of your table by using IFERROR to get rid of Excel’s “#N/A” error message.

While this is clearly a great use case for IFERROR, the key problem with using the formula is this:

IFERROR masks ALL possible Excel error types, even the ones you were not originally intending to mask

The IFERROR Formula & Syntax

Let’s start by reviewing the IFERROR formula and its syntax:

= IFERROR ( value , value_if_error )

The syntax itself is fairly simple.  Where it asks you for the value, put in your original intended formula, and where it asks for value_if_error, input the value you want to show if that original formula returns an error.

In the example above, we were using a simple VLOOKUP to pull a value from a table.  To add IFERROR to that formula, all I need to do is add the IFERROR formula prompt and indicate the value I want to show for the error condition, which is in this case “Not Found“.

The Problem with IFERROR

IFERROR becomes problematic when you encounter an error condition in your data set that you were not expecting.  We’ll continue from the previous example, but this time show the source data table that the VLOOKUP was pulling from.  In the image below, we have the same IFERROR setup to lookup values from one table to another.

You can see that Seattle and Los Angeles are pulling correctly because they both appear in the source table.

Detroit and Mexico City are both also calculating correctly, because they do not appear in the source and therefore are indicated with “Not Found“.

The problem lies with San Francisco, which shows “Not Found” but is actually there in the source table; the value just happens to have a #DIV/0! error in it.  Because IFERROR masks all error types, it ends up implying that San Francisco is not in the source table at all.

This IFERROR issue can cause huge issues, as it is not limited to just masking #DIV/0 errors.  The errors for #REF!, #NAME?, #VALUE! (just to name a few), all of which are worth your attention, would all also be masked by the IFERROR formula.  For any data sensitive situation, I’d rather have these error messages pop-up and tell me that I need to fix my data set, rather than show a table that is aesthetically pleasing, but fundamentally incorrect.

Using IF ISNA to Mask #N/A Errors

The key way to bypass the IFERROR issue, particularly for lookup formulas, is to use the IF ISNA combination formula.  The syntax is fairly simple and it was the method people generally used before the IFERROR formula was available.

= IF ( ISNA ( original formula ) , value_if_error , original formula )

If we were to apply the IF ISNA approach to the same formula in the prior examples, it would look like this:

And you can see, the formula is now pulling the correct value for San Francisco, indicating that it is in our source data table, but has a divide by zero error.

The key benefit of using IF ISNA is that it looks specifically for the #N/A error in Excel.  If and only if that error occurs, then Excel will display your error condition.

However, you’ll also notice that, because you have to input your original formula twice, the formula for IF ISNA is much longer and therefore takes longer to input.  This is likely one of the primary selling point for IFERROR, in that, it takes much less time to input the formula because you don’t have to write your original formula twice.

Conclusion

IFERROR is an efficient way to streamline the error checking process, especially if you’re working on a data set that:

• You are familiar with and understand all of the potential errors within it
• The errors you do expect are relatively benign
• Your main goal is to get a quick, clean initial summary before diving in

However, using IFERROR can cause problems down the road if you have a data set that:

• Is too large for you to fully understand all the possible errors within it
• Will evolve over time, potentially incorporating new unexpected errors
• Your final output requires a high level of precision, void of any mistakes

Overall, my opinion is to avoid using IFERROR whenever possible.  It’s better to be direct about the different types of errors you are expecting.  For example, specifically denote what you want the cell to show if you encounter a divide by zero error (If denominator = 0) or not available error (IF ISNA).  This way you don’t accidentally mask an error that you weren’t expecting.

While IFERROR is definitely efficient and also very intuitive, it’s not always the best formula to use to mask errors because it can clearly cause problems down the line.