Open In App

Hide Error Values and Indicators in Cells in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

While working with formulas in spreadsheets, we might get error values. These error values are often shown in the cells as error indicators. Sometimes, they are helpful in resolving errors but other times when errors need not be corrected, these error values just make the spreadsheet look cluttered. In such a situation, it is better to hide these error indicators to not only make the spreadsheet look clean but also to prevent these errors from stopping other formulas to work correctly. Let us discuss the different ways in which we can do that but first let us look at the problem statement with an example. 

Hiding Error Values and Indicators in MS Excel

Open a new excel sheet and follow the steps given below.

Step 1: Go to the Math and Trig section on the formulas tab and click on the quotient option from the drop-down menu. You must see a pop-up like this:

Select the Quotient formula from Math and trig Option.

Select the Quotient formula 

Step 2: In this pop-up, enter 4 in the numerator and 0 in the denominator. 

Enter values in numerator and denominator.

Enter values in the pop-up

Step 3: Click ok. You will get an error like this: 

Get a DIV error.

The div error 

You can see how this error is indicated with an exclamatory mark since division by 0 is not defined. Some other error values can be #NAME?, #NULL!, and #REF!, etc. 

Before we discuss the various ways in which we can hide these error values, let us first see how to deal with error indicators. 

Change the Excel settings to Hide Error Indicators

As you can see in the above example, whenever a cell contains an error, a triangle appears in the top-left corner. This is nothing but an error indicator. Follow the steps given below to hide these error indicators:

Step 1: Go to file tab and select options. 

Go to file and select option.

Step 1

Step 2: A dialog box will appear. In this box, go to the formulas section and deselect the enable background checking box under the error checking section. 

Go to the formulas section and deselect the enable background checking box.

Step 2

Step 3: Click ok. 

This was to hide the error indicators, now onto error values. 

Use the IFERROR function to Convert the Error to Zero

Let us first enter =DIV(6/3) in the cell A1 of an excel spreadsheet. 

Enter =DIV(6/3) in the cell A1.

Create an error 

Then, click on enter and you will see an error like this:

Get a Name error.

#NAME error 

To hide this error, follow the steps given below:

Step 1: Select cell A1 and click the F2 button. This is let you edit the formula. 

Step 2: Now, to this formula, add the following function along with a 0 at the end as shown below.

Write the IFERROR function in cell A1.

IFERROR function 

Step 3: Once you click enter, you will see a 0 in place of the #NAME? error. 

If you wish, you can hide this 0 as well by using conditional formatting. 

Hide the Zero with the help of the Conditional Format

Follow the below steps in continuation to hide the 0 present in cell A1.

Step 1:  Select cell A1 and click on the conditional formatting option present under the home tab. 

Select the cell and click on the conditional formatting

Conditional formatting option on the home tab 

Step 2: From the list, select the New Rule option. You will see a dialog box like this:

New Formatting Rule dialog box appear.

New Rule Dialog box 

Step 3: Inside this dialog box, select Format only cells that contain option from the rule type, and under the edit rule description section, the first cell should contain Cell value, the second cell should contain equal to and enter 0 in the last cell. 

Select a Rule type and Edit the rule description.

New Formatting rule 

Step 4: Click the format button. You will see a dialog box with the name Format Cells appears. In this dialog box, click on the number tab, and select custom from the category section. Also, under the type section, enter three semicolons like this ;;; as shown below:

In the format cell Select custom then in type write ;;;.

Format Cells 

Step 5: Now, click ok. Click ok again. You can see that the 0 in cell A1 disappears after this. 

We can also use the conditional formatting option to turn the text white so that the erroneous text is not visible. 

Use Conditional format to turn text white and hide error values

To turn the text white using a conditional format, follow the below steps:

Note that we are using the same =DIV(6/3) error as above to explain this example. 

Step 1: Select the cell or the range of cells containing error and click on the conditional formatting option present under the home tab.

Select the cell and click on the conditional formatting

Conditional formatting 

Step 2: From the list, select the New Rule option. You will see a dialog box like this:

New Formatting Rule dialog box appear.

New formatting rule 

Step 3: Inside this dialog box, select Format only cells that contain option from the rule type, and in the edit rule description section, select errors from the first dropdown. 

Select a Rule type and Edit the rule description.

New formatting rule 

Step 4: Click on format. A new dialog box will appear. Go to the font section in this dialog box. 

Click on Format and go to the font,

Font 

Step 5: On the font tab, choose a white color from the color drop-down.

choose white color from the color drop down.

Color drop down

 Step 6: Click ok. Click ok again. 

Replace the Error with NA or a dash 

We can use the IFERROR and NA functions to replace an error with the string NA, #N/A, or a dash(-). 

The IFERROR function 

The IFERROR function of excel is used to check if there is an error in a cell or a formula. If the error is found, the IFERROR function returns the value you specify. Otherwise, it simply returns the result of the formula. 

Syntax

IFERROR(formula, replacement_value)

The function takes two arguments. 

1. formula – It is the formula that has to be evaluated for errors. 

2. replacement_value – It is the value that is returned when the formula evaluates to an error. The error can be #N/A, #REF!, #NULL!, #NAME?, #DIV/0! and #VALUE!.

Use the IFERROR function to replace the error with a dash or the string NA

Let us try this out on the first example error that we saw above. 

Step 1: Double-click in cell A1 to edit the formula. Edit the formula as follows:

Double-click in the cell for edit the formula.

Edit the formula 

Step 2: Press enter. You will see that the error is replaced by the string NA. 

Error is replaced by the string NA

NA appears 

If you want to display a dash instead of the string NA, then change the formula as follows:

=IFERROR(QUOTIENT(4,0), “-“)

The NA functions 

This function simply returns #N/A. This is an error value that means ‘no value is available’. This helps in marking empty cells. 

Syntax

NA()

This function has no arguments. 

Use the NA function along with the IFERROR function to replace the error with #N/A

Just like we replaced an error value with a string or dash with the help of the IFERROR function, we can replace it with the value #N/A with the help of these two formulae:

=IFERROR(QUOTIENT(4,0), NA())

This is done below:

Step 1: Double-click in the cell A1 to edit the formula. Edit the formula as follows:

Double-click in the cell for edit the formula.

The formula

Step 2: Press Enter, and then you get the result.

Error is replaced by the string #N/A

The result 



Last Updated : 23 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads