Hide error values and error indicators in cells

Let's say that your spreadsheet formulas have errors that you anticipate and don't need to correct, but you want to improve the display of your results. You have several ways to hide error values and error indicators in cells.

Formulas can return errors for a number of reasons. For example, division by 0 is not allowed, and if you enter the formula =1/0, Excel returns #DIV/0. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

Convert an error to zero and use a format to hide the value

You can hide error values by converting them to a number such as 0, and then applying a conditional format that hides the value.

Create an example error

1. Open a blank workbook, or create a new worksheet.

2. Enter 3 in cell B1, enter 0 in cell C1, and in cell A1, enter the formula =B1/C1.
   The #DIV/0! error appears in cell A1.

3. Select A1, and press F2 to edit the formula.

4. After the equal sign (=), type IFERROR followed by an opening parenthesis.
   IFERROR(

5. Move the cursor to the end of the formula.

6. Type ,0) – that is, a comma followed by a zero and a closing parenthesis.
   The formula =B1/C1 becomes =IFERROR(B1/C1,0).

7. Press Enter to complete the formula.
   The contents of the cell should now display 0 instead of the #DIV! error.

Apply the conditional format

1. Select the cell that contains the error, and on the Home tab, click Conditional Formatting.

2. Click New Rule.

3. In the New Formatting Rule dialog box, click Format only cells that contain.

4. Under Format only cells with, make sure Cell Value appears in the first list box, equal to appears in the second list box, and then type 0 in the text box to the right.

5. Click the Format button.

6. Click the Number tab and then, under Category, click Custom.

7. In the Type box, enter ;;; (three semicolons), and then click OK. Click OK again.
   The 0 in the cell disappears. This happens because the ;;; custom format causes any numbers in a cell to not be displayed. However, the actual value (0) remains in the cell.

What else do you want to do?

Hide error values by turning the text white

Display a dash, #N/A, or NA in place of an error value

Hide error values in a PivotTable report

Hide error indicators in cells

Hide error values by turning the text white

You can also hide error values by turning the text white, or otherwise making the text match the background color of your worksheet.

1. Select the range of cells that contain the error value.

2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

3. Click New Rule. The New Formatting Rule dialog box appears.

4. Under Select a Rule Type, click Format only cells that contain.

5. Under Edit the Rule Description, in the Format only cells with list, select Errors.

6. Click Format, and then click the Font tab.

7. Click the arrow to open the Color list, and under Theme Colors, click the white box.

Top of Page

Display a dash, #N/A, or NA in place of an error value

In addition to hiding errors, you can replace them with values such as NA. To do this, you can use the IFERROR and NA functions, as the following example shows.

The example may be easier to understand if you copy it to a blank worksheet. In the example below, select the cell that contains the word "Data" and then drag the cursor to select through the last cell in the "Description (Result)" column. Then, copy the selection and paste it into a new worksheet, beginning at cell A1.

Data	Formula	Description (Result)
10	=A2/A3	Results in an error (#DIV/0)
0	=IFERROR(A2/A3,"NA")	Returns NA when the value is an error
	=IFERROR(A2/A3,"-")	Returns a dash when the value is an error
	=IFERROR(A2/A3,NA())	Returns #N/A when the value is an error

Function details

IFERROR     Use this function to determine if a cell contains an error or if the results of a formula will return an error.

NA    Use this function to return the string #N/A in a cell. The function name is followed by a pair of empty parentheses, like this: =NA().

Top of Page

Hide error values in a PivotTable report

1. Click the PivotTable report. The PivotTable Tools appear.

2. On the Analyze tab, in the PivotTable group, click the arrow next to Options, and then click Options.

3. On the Layout & Format tab, do one or more of the following:

   • Change error display     Under Format, select the For error values show check box, then type the value you want to display. To display errors as blank cells, delete any characters in the box.

   • Change empty cell display     Select the For empty cells show check box. In the box, type the value you want to display in any empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Top of Page

Hide error indicators in cells

If a cell contains a formula that results in an error, a triangle (an error indicator) appears in the top-left corner of the cell. You can prevent these indicators from being displayed by using the following procedure.

Cell with a formula problem

1. Click File, click Options, and then click the Formulas category.

2. Under Error Checking, clear the Enable background error checking check box.

Top of Page