Hide error values and error indicators in cells

Microsoft Office Excel 2003

previous page next page
  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.

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

Use the IF, ISERROR, and NA functions to do this task.

  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)

Worksheet example

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

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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

ShowHide 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 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 you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

ShowHide 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.
previous page start next page