How to hide values and error flags in Microsoft Excel.
Contents
Your Excel formulas can occasionally produce errors that do not need to be fixed. However, these errors can seem messy, and more importantly, prevent other Excel formulas or functions from working properly. Fortunately, there are ways to hide these error values.
Hide errors with the IFERROR function
The easiest way to hide error values in your spreadsheet is with the IFERROR function. With the IFERROR function, you can replace the displayed error with another value, or even an alternative formula.
In this example, a VLOOKUP function has returned the #N/A error value.
This error occurs because there is no office to search 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. Requires the value to check for an error and what action to take instead of the error if found.
In this example, the VLOOKUP function is the value to check, and “0” is returned instead of the error.
Using “0” instead of the error value ensures that the other calculations and potentially other features, such as graphics, work correctly.
Background error checking
If Excel suspects an error in your formula, a small green triangle appears in the upper left corner of the cell.
Note that this flag does not mean that there is definitely an error, but rather that Excel is looking at the formula you are 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 the cell, an icon appears, warning you of a possible error in your formula.
Click on the icon to see different options to handle the alleged error.
In this example, the indicator appeared because the formula skipped adjacent cells. The list provides options to include skipped cells, ignore the error, find more information, and also change error checking options.
To remove the indicator, you must either correct the error by clicking “Update formula to include cells” or ignore it if the formula is correct.
Turn off Excel error checking
If you don’t want Excel to warn you of these potential errors, you can turn them off.
Click File > Options. Then select the “Formulas” category. Uncheck the “Enable background bug check” box to disable all background bug checks.
Alternatively, you can disable specific bug checks from the “Bug Check Rules” section at the bottom of the window.
By default, all error checking is enabled except “Formulas referencing empty cells”.
More information about each rule can be accessed by hovering the mouse over the information icon.
Check and uncheck the boxes to specify which rules you would like Excel to use with background error checking.
When formula errors don’t need to be fixed, their error values should be hidden or replaced with a more useful value.
Excel also performs error checking in the background and sees what mistakes it thinks you’ve made with your formulas. This is useful but specific or all error checking rules can be turned off if they interfere too much.
