Highlight Rows Based on a Cell Value in Excel

Hide Error Values and Indicators in Cells in Excel

Last Updated: 23 Jan, 2023

Everything tech

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

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:

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.

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.

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.

Create an error

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

#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.

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 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:

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.

Conditional formatting

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

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.

New formatting rule

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

Font

Step 5: On the font tab, choose a 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:

Edit the formula

Step 2: Press enter. You will see that the 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: