How to Optimize a Workbook with IFERROR in Excel

The best error handling function is IFERROR (in Excel). Let’s explore its applications. To show how much this can impact the readability of your workbook, I’ll focus on a specific example through the guide (including a video with tutorials on practical applications). My examples create an imaginary company with employee IDs and their corresponding data. I include a few formulas that show various errors, then I show how to use IFERROR in Excel to make the errors readable for a layman.

By the end of this article, you’ll have a clear understanding of how to handle errors efficiently in your spreadsheets.

The Excel IFERROR Function Data Set

Before we delve into the IFERROR function, let’s look at our example data set. You can access the dataset here.

Note that the IFERROR dataset contains information on:

• Employee ID
• Name
• Department
• Salary
• Total bonuses paid
• Years worked at the company

With this data, let’s tackle some typical scenarios with the IFERROR worksheet function.

Calculating Average Yearly Bonus: #DIV/0 Errors Example

First, I want to know the average yearly bonus for each employee. To do this, I’ll divide the total bonuses paid by the number of years worked. For instance, if an employee has received $5,000 in bonuses over 10 years, their average yearly bonus is $500.

By dragging this formula down for each employee, we can quickly calculate their average yearly bonuses based on the appropriate range of cells.

But what happens when there’s an inconsistency, such as bonuses paid but no years worked? This generates a division by zero error, denoted as #DIV/0.

Video Explainer: IFERROR Formula Examples

Here’s a video I made on YouTube that explains exactly how to use =IFERROR() in Excel. I’m including the same examples here that I used in the video.

If you have any questions about the examples I use here (or in the video), please let me know in the comments.

Applying IFERROR to Handle Division by Zero

To address this, we use the IFERROR function. This function lets you specify what should appear if an error is encountered. Here’s how you do it:

1. In your formula, wrap the existing division formula in IFERROR.
2. Use the syntax: IFERROR(YourFormula, "ErrorMessage").
3. For instance, =IFERROR(Bonuses/YearsWorked, "less than one year worked").

Note that the first argument of the IFERROR function asks for the formula you’re checking. The second argument gives your desired result if that formula returns any error.

So, any division-by-zero errors will display “less than one year worked” instead of an error message. There’s also a way to show nothing instead of the error message.

If you want to have a blank cell instead of the messy #VALUE, #NA, #NUM!, or #DIV/0 errors, just use an empty string in the “ErrorMessage” part of the syntax. For the example above, that would look like this:

=IFERROR(Bonuses/YearsWorked, " ")

That returns a null space, also called an empty string, instead of the error message.

Using IFERROR to Resolve #DIV/0 Recap:

• Original formula: =TotalBonuses/YearsWorked
• Wrapped with IFERROR: =IFERROR(TotalBonuses/YearsWorked, "less than one year worked")

Best Practices Tip: Use the Excel IFERROR function to trap errors and show more helpful information in the cell.

Using IFERROR with Lookup Functions

Now, let’s look at another application: using IFERROR in lookup formulas like VLOOKUP. Suppose we have an employee ID and want to find the employee’s current salary. Here’s a quick guide to writing our VLOOKUP function where you can provide useful text with a positive value argument:

=VLOOKUP(EmployeeID, DataRange, ColumnIndex, FALSE)

If the employee ID doesn’t exist in the data set, it results in an N/A error. To handle this, we incorporate IFERROR to replace an error lookup value with something more helpful:

=IFERROR(VLOOKUP(EmployeeID, DataRange, ColumnIndex, FALSE), "Invalid EEID")

Now, if the employee ID is not found, it will display “Invalid EEID” instead of an error.

Lookup Functions Example Recap:

1. Original VLOOKUP formula: =VLOOKUP(EmployeeID, DataRange, 4, FALSE)
2. Wrapped with IFERROR: =IFERROR(VLOOKUP(EmployeeID, DataRange, 4, FALSE), "Invalid EEID")

Best Practices Tip: Keep your error message responses short and to-the-point. Invalid EEID works well for this example because it says exactly what’s wrong and why the expected number isn’t shown.

Using IFERROR with an Array of Values

Another great application of the IFERROR function is with array formulas. Suppose you’re calculating the average of an array of values but want to ensure that any errors in the array, such as missing or invalid data, don’t disrupt your formula. You can use the IFERROR function to clean up the array of results.

Simply wrap your array formula in IFERROR like so: =IFERROR(AVERAGE(ArrayRange), "Invalid Data").

This way, if any error occurs in the array, the formula will return “Invalid Data” rather than producing an error message. That can make your workbook much more readable, especially for someone not familiar with Excel’s many varied error expressions.

Other Error-Handling Options

If you want to identify just different types of errors (including specific errors), you might try one of the following:

• ISNA: The ISNA function asks whether a cell contains the #N/A error message.
• IFNA: If you want to identify #N/A errors and provide an alternate result, use =IFNA in Excel instead. The IFNA function works in a very similar way to the IFERROR function.
• ISERROR: This returns a true/false whether there’s an error in a cell. It’s more general than =ISNA(). Note that we have a full guide on the ISERROR function.
• ISERR: This Microsoft Excel function returns true for any type of error except #N/A errors.

Any of these could be useful if you’re looking to trap errors in a specific way.

Conclusion

By efficiently harnessing the IFERROR function, you can cleanse your data, prevent those ominous-looking error messages, and ensure your outputs are as informative as they are accurate. Whether you’re dividing numbers or performing lookups, IFERROR can save time and improve the readability of your spreadsheets.

For more spreadsheet tips and tricks, including detailed examples for common challenges, check out SpreadsheetPoint.com or our YouTube channel.