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.


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.


The IFERROR function has two arguments, as described below.

=IFERROR(value, value_if_error)


  • 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.


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!


To download the file used in these screenshots: Iferror


Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. mano on October 2, 2014 at 4:27 pm

    Hi Jeff dear,
    thank you very much because learn excel trick
    kind regard

  2. MT on August 31, 2015 at 12:46 am

    for look up with inconsistent format within a look up column

    • jefflenning on September 3, 2015 at 8:20 am

      Nice application…thanks for sharing!

Leave a Comment