When I first found out about IFERROR, I was a little confused. “There’s a formula to use if we generate an error? Isn’t the point of Excel to avoid errors altogether?” I quickly found out how naive I was.

Whether you’re conducting a MATCH test to see if your data exists in another table or just dividing two numbers, you can get errors. When you’re working with hundreds or thousands of lines of data, its not always practical to go through one by one and see what types of errors your formulas are generating. You may just want to know if there is an error. If that’s the case, errors might be totally acceptable using the formula you wrote, but you don’t want to deliver an Excel spreadsheet with a large number of cells containing “#N/A” or “#DIV/0!”.

Example 1: Dividing by Zero

In this example, we’re diving column A by column B to produce column C. Further down column C, we have some “#DIV/0!” errors, which just means we divided by zero. In this case, we can use IFERROR to change the result of those errors.

In this example, we’ve simply programmed the formula to spit out the word “ERROR” if one is found. At the very least, your resulting data looks cleaner. Depending on the case, you may have to check your data in Column B because there should be no errors. However, if it is acceptable for column B to have a value of zero, your formula in column C now produces a much cleaner result.