How to Hide Error Values and Indicators in Microsoft Excel

How to Hide Error Values and Indicators in Microsoft Excel


Linux Articles / All 76 Views comments

Excel Logo

Your Excel formulation can sometimes produce errors that don’t want fixing. Nevertheless, these errors can look untidy and, extra importantly, cease different formulation or Excel options from working appropriately. Fortuitously, there are methods to cover these error values.

Disguise Errors with the IFERROR Perform

The simplest method to disguise error values in your spreadsheet is with the IFERROR perform. Utilizing the IFERROR perform, you'll be able to substitute the error that’s proven with one other worth, and even an alternate method.

On this instance, a VLOOKUP perform has returned the #N/A error worth.

#N/A error shown from VLOOKUP

This error is because of there not being an workplace to search for. A logical cause, however this error is inflicting issues with the entire calculation.

The IFERROR perform can deal with any error worth together with #REF!, #VALUE!, #DIV/zero!, and extra. It requires the worth to verify for an error and what motion to carry out as an alternative of the error if discovered.

Requirements of the IFERROR function

On this instance, the VLOOKUP perform is the worth to verify and “zero” is displayed as an alternative of the error.

IFERROR function to display 0 instead of error

Utilizing “zero” as an alternative of the error worth ensures the opposite calculations and probably different options, reminiscent of charts, all work appropriately.

Error values hidden with IFERROR

Background Error Checking

If Excel suspects an error in your method, a small inexperienced triangle seems within the top-left nook of the cell.

Green indicator of possible Excel error

Notice that this indicator doesn't imply that there's undoubtedly an error, however that Excel is querying the formulation you’re utilizing.

Excel mechanically performs quite a lot of checks within the background. In case your formulation fails certainly one of these checks, the inexperienced indicator seems.

Whenever you click on on the cell, an icon seems warning you of the potential error in your method.

Smart tag for error options

Click on the icon to see totally different choices for dealing with the supposed error.

Options for handling the error

On this instance, the indicator has appeared as a result of the formulation has omitted adjoining cells. The listing supplies choices to incorporate the omitted cells, ignore the error, discover extra info, and in addition change the error examine choices.

To take away the indicator, you'll want to both repair the error by clicking “Replace Formulation to Embrace Cells” or ignore it if the method is right.

Flip Off the Excel Error Checking

If you don't want Excel to warn you of those potential errors, you possibly can flip them off.

Click on File > Choices. Subsequent, choose the “Formulation” class. Uncheck the “Allow Background Error Checking” field to disable all background error checking.

Error checking options

Alternatively, you'll be able to disable particular error checks from the “Error Checking Guidelines” part on the backside of the window.

By default, all the error checks are enabled besides “Formulation Referring to Empty Cells.”

Turn off specific error checking rules

Extra details about every rule could be accessed by positioning the mouse over the knowledge icon.

More information on error checks

Examine and uncheck the packing containers to specify which guidelines you want to Excel to make use of with the background error checking.

When formulation errors don't want fixing, their error values ought to be hidden or changed with a extra helpful worth.

Excel additionally performs background error checking and queries errors it thinks you’ve made together with your formulation. That is helpful however particular or all error checking guidelines may be disabled in the event that they intrude an excessive amount of.