How to trap errors in formulas

In this video, we'll look at a few ways to trap errors. Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use.

In this first worksheet, we have a simple table that shows test scores for 5 sections. The total questions in each section are in column C, and the total questions that were correct are in column D.

Column E contains a formula that calculates the percentage score in each section by dividing the correct answers by the total number of questions in the section.

Notice we get a Divide by Zero error in column E for the last 2 sections. That's because there is no data yet to work within those sections.

If you want to trap and suppress this error, there are a few easy options.

Let's go over three of these options in Columns G, H, and I.

First, you could test for a blank value in column C using the IF function with the ISBLANK function.

=IF(ISBLANK(C6),"",D6/C6)

For the logical test use ISBLANK and C6 for the reference. For [value_if_true], use empty double quotes, and for [value_if_false], use the original formula: D6/C6.

When I copy this formula down, we get the correct result for the first 3 sections and nothing is displayed for the last two sections.

This is a flexible way to trap errors since you are checking the source cells for any condition that you like. You can easily test for empty values, positive values, or values that match any condition that makes sense.

Another way to trap this error is to use the IFERROR function which was introduced with Excel 2007.

IFERROR takes two arguments, value and value_if_error. For value, we supply the original formula, and for value_if_error, use empty double-quotes.

IF value is an error—that is if our formula returns an error—IFERROR will catch it and return the empty string. If there's no error, IFERROR will simply display the value; in this case, the result of the formula.

If you're using an older version of Excel you can also test for an error using the IF function and the ISERROR function, which will return TRUE with any error.

In this case, the logical test is the ISERROR with the original formula. The value if true is empty double quotes, and the value if false is the original formula.

=IF(ISERROR(D6/C6),"",D6/C6)

This works fine, but the structure is redundant and more error-prone compared to IFERROR, so you're better off using IFERROR when possible.

Finally, although we're using empty double quotes to display nothing when we detect an error condition, you could also display any message that you like. For example, you could display "not enough data" instead of an empty cell, or any other message that makes sense for the error that you're trapping.