How to Trap Error Messages in Excel using the IF Error Function

In last week’s tip we had a look at some common Excel errors such as #VALUE! or #DIV/0! and how to correct them.

If you work with formulas, you’ve probably encountered a formula error; this means you need to track down the source of the error and fix it. But sometimes a formula error simply means that the data used by the formula is not yet available.

Applies To: Microsoft® Excel® 2010 and 2013

Let’s take a look at an example in the sales industry. It might be a requirement of you in telemarketing to track your daily sales as a percentage of calls made.

IF-error-function

  • The formulas set up in column D would bring up the numeric result that you see. Cell D4 contains the formula =C4/B4.
    The answer was then converted to percentages by using the percent style option.
  •  The formula works as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is not allowed. As a result, the #DIV/0! error message is displayed which can make your worksheet look cluttered.
  •  You can avoid displaying formula errors by re-writing your formula to use an IFERROR function. For example:
    =IFERROR((C9/B9);””)

IF-error-function-2

  •  This will display a blank cell if the division operation results in an error (cell B9 is empty or contains 0), yet still displays valid results
  •  If you prefer, you can replace the empty string (“”) with other text of your choice–just make sure the text is enclosed in quote marks.
    =IFERROR((C9/B9), “Please check your formula”)