Clean up Reports with IFERROR

In the old days, we could use a combination of the IF and ISERROR functions to clean up reports. This method was good, and worked for many years…but starting with Excel 2007 there is an easier alternative. This post explores the IFERROR function.

Overview

The IFERROR function enables us to use a single function to clean up Excel errors in our workbooks. Let’s use an example. We are preparing a variance report that compares current year to prior year amounts. For each account, we include current year, prior year, and increase (decrease) columns, as shown below.

When we add a variance percentage column, we receive an Excel error (#DIV/0!) for accounts with zero prior year amounts, such as Trade shows and Travel, as shown below.

Fortunately, this report is easily cleaned up with the IFERROR function.

Syntax

The IFERROR function has two arguments, as described below.

=IFERROR(value, value_if_error)

Where:

• Value will be returned if it is not an error
• Value_if_error will be returned if the first argument is an error

This allows us to substitute a specified value for an error. Rather than return an error, we could tell Excel to return a zero, a dash, or any other value, text string, or function. If we wanted the variance column to present a zero rather than the #DIV/0! error, we could use the following formula:

=IFERROR(E7/D7, 0)

This results in the following, much cleaner, report:

If you preferred to return a dash or an “n/a” string instead of a zero, just update the second argument accordingly.

Conclusion

Using an IFERROR function to clean up reports is a great approach. Please share your thoughts or alternatives to the IFERROR function by posting a comment below…thanks!

Notes

To download the file used in these screenshots: Iferror