Hide error values and error indicators in cells

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

There are many reasons why formulas can return errors. For example, division by 0 is not allowed, and if you enter the formula =1/0, it returns #DIV/0. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

What do you want to do?

Format text in cells that contain errors so that they don't show

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


Format text in cells that contain errors so that they don't show

  1. Select the cells that contain the error value.
  2. On the Format menu, click Conditional Formatting.
  3. In the box on the left, click Formula Is.
  4. In the box on the right, type =ISERROR(reference), where reference is a reference to the cell that contains the error value.
  5. Click Format, and then click the Font tab.
  6. Click Format.
  7. In the Color box, select white.

Top of Page Top of Page


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

To do this task, use the IF, ISERROR, and NA functions.

  1. Select the cell that contain the error value.
  2. Wrap the following formula around the formula in the cell, where old_formula is the formula that was previously in the cell.

=IF(ISERROR(old_formula),"",old_formula)

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
 
1
2
3
A
Data
10
0
Formula Description (Result)
=A2/A3 Results in an error (#DIV/0)
=IF(ISERROR(A2/A3),"NA",A2/A3) Returns NA when the value is an error
=IF(ISERROR(A2/A3),"-",A2/A3) Returns a dash when the value is an error
=IF(ISERROR(A2/A3),NA(),A2-A3) Returns #N/A when the value is an error

Function details

IF

ISERROR

NA

Top of Page Top of Page


Hide error values in a PivotTable report

  1. Click the report.
  2. On the PivotTable toolbar, click PivotTable, and then click Table Options.
  3. Do one or more of the following:

Change error display     Select the For error values, show check box under Format options. In the box, type the value that you want to display instead of errors. 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 that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Top of Page Top of Page


Hide error indicators in cells

If a cell contains a formula that breaks one of the rules, a triangle appears in the top-left corner of the cell. You can prevent these indicators from being displayed.

Cell with a formula problem

Cell with a formula problem
  1. On the Tools menu, click Options and then click the Error Checking tab.
  2. Clear the Enable background error checking check box.

Top of Page Top of Page

 
 
Applies to:
Excel 2003