Replace errors in Excel

  • Article

If you're tired of seeing error messages in your Excel 2007 cells, there's a quick way to change them to whatever you'd like to appear. Just use IFERROR.

Excel error and replacements

In this example, the Percent column has four results.

In C2, you can see a correct result.

In C3, there's an ugly divide by zero error (caused, in this case, by a mistake in the formula - but no matter).

If you'd rather not see this type of error (or any others), add IFERROR to your functions, like so:

  • To return a number, such as the 0.00% in C4, use: =IFERROR(B4/B8,0)
  • To return text, use quotes around the words you want to appear such as the N/A that appears in C5: =IFERROR(B3/B7,"N/A")

In both cases, the first value is your formula and the second value is what to return when there's an error returned by your formula.

This will make it harder to find actual mistakes. But if you're sure of your work and want to suppress the errors for those cases when, say, division by zero is expected, this will do the trick.

Suzanne