Prevent Errors From Appearing in Excel

How to prevent errors from appearing in formulas in Excel. This is especially helpful for the Vlookup function.

Sections:

Excel 2007 and Later

Excel 2003 and Earlier

Notes

Excel 2007 and Later

This is very easy to do in Excel 2007 and later using the IFERROR() function.

Syntax

=IFERROR(value, value_if_error)

The value is the function or formula that you would normally put into the cell and the value_if_error argument is the output that you want to appear when there would otherwise be an error output.

Example

=IFERROR(1/0,"Custom Error Output")

Here, I am trying to divide 1 by 0, which is impossible and which will return an error.

If I put this formula into a cell without IFERROR it will look like this:

Note that you do need the equal sign when putting a formula into a cell but you do not need it when putting it into the IFERROR function.

The result is this:

In cell A1, you can see the nice custom error message from the IFERROR function and in cell A2, you can see the original nasty error message.

Using IFERROR, you can output whatever you want when there is an error. You simply fill-out the value_if_error argument in one of the following ways:

Output Text

Put text surrounded by double quotation marks like in the example above.

Output a Blank

Put double quotation marks with nothing between them.

Output a 0

Type a 0 or leave the argument blank.

Output a Number

Type the desired number.

Don't forget that for the first argument (value) you can input any formula or function that you want. I kept the example here simple so you wouldn't focus on the formula that caused the error. However, this setup is perfect for the VLOOKUP function, which returns an error when the lookup_value argument is empty.

Excel 2003 and Earlier

There is no IFERROR function in Excel 2003 and earlier. We need to use the IF function combined with the ISERROR function.

=IF(ISERROR(1/0),"Custom Error Output",1/0)

I am using 1/0 for the formula that will cause an error because it is simple and throws an error.

Here, you can see that the ISERROR function is put inside the IF function and that we have to write the formula that causes, or might cause, an error twice. When you are working with really long formulas, this can be a real annoyance, but it is the only way to hide default error output in Excel 2003 and have your own custom error message for formulas.

I used 1/0 for the error causing formula but you can use whatever formula or function you need. Simply wrap that formula/function within the IF and ISERROR functions like in the example above; remember that you will have to put it twice and that you do not put the equals sign in front of it since we are nesting it within a formula.

Just like the example for Excel 2007 and later, you can output a variety of different things, be that text, numbers, 0, or a blank. Just replace "Custom Error Output" from the above formula with one of these options:

Output Text

Put text surrounded by double quotation marks like in the example above.

Output a Blank

Put double quotation marks with nothing between them.

Output a 0

Type a 0 or leave the argument blank.

Output a Number

Type the desired number.

Notes

Preventing errors from appearing in Excel was made much easier starting with Excel 2007. It can be done in Excel 2003, but it can be very difficult to maintain when you have a long and complex formula for which you don't want an error to display. That said, you now know how to suppress and customize error message for formulas and functions in Excel, regardless of the version of Excel that you use.

Make sure to download the sample spreadsheet attached to this tutorial to work with this in Excel.