Excel Logo

Your Excel formulas can occasionally produce errors that don’t need fixing. However, these errors can look untidy and, more importantly, stop other formulas or Excel features from working correctly. Fortunately, there are ways to hide these error values.

Hide Errors with the IFERROR Function

The easiest way to hide error values on your spreadsheet is with the IFERROR function. Using the IFERROR function, you can replace the error that’s shown with another value, or even an alternative formula.

In this example, a VLOOKUP function has returned the #N/A error value.

#N/A error shown from VLOOKUP

This error is due to there not being an office to look for. A logical reason, but this error is causing problems with the total calculation.

The IFERROR function can handle any error value including #REF!, #VALUE!, #DIV/0!, and more. It requires the value to check for an error and what action to perform instead of the error if found.

Requirements of the IFERROR function

In this example, the VLOOKUP function is the value to check and “0” is displayed instead of the error.

IFERROR function to display 0 instead of error

Using “0” instead of the error value ensures the other calculations and potentially other features, such as charts, all work correctly.

Error values hidden with IFERROR

Background Error Checking

If Excel suspects an error in your formula, a small green triangle appears in the top-left corner of the cell.

Green indicator of possible Excel error

Note that this indicator does not mean that there is definitely an error, but that Excel is querying the formula you’re using.

Excel automatically performs a variety of checks in the background. If your formula fails one of these checks, the green indicator appears.

When you click on the cell, an icon appears warning you of the potential error in your formula.

Smart tag for error options

Click the icon to see different options for handling the supposed error.

Options for handling the error

In this example, the indicator has appeared because the formula has omitted adjacent cells. The list provides options to include the omitted cells, ignore the error, find more information, and also change the error check options.

To remove the indicator, you need to either fix the error by clicking “Update Formula to Include Cells” or ignore it if the formula is correct.

Turn Off the Excel Error Checking

If you do not want Excel to warn you of these potential errors, you can turn them off.

Click File > Options. Next, select the “Formulas” category. Uncheck the “Enable Background Error Checking” box to disable all background error checking.

Error checking options

Alternatively, you can disable specific error checks from the “Error Checking Rules” section at the bottom of the window.

By default, all of the error checks are enabled except “Formulas Referring to Empty Cells.”

Turn off specific error checking rules

More information about each rule can be accessed by positioning the mouse over the information icon.

More information on error checks

Check and uncheck the boxes to specify which rules you would like Excel to use with the background error checking.

When formula errors do not need fixing, their error values should be hidden or replaced with a more useful value.

Excel also performs background error checking and queries mistakes it thinks you’ve made with your formulas. This is useful but specific or all error checking rules can be disabled if they interfere too much.

Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »