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

  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 is displayed.

  3. Click New rule.

    The New Formatting Rule dialog box is displayed.

  4. Under Select a Rule Type, click Use a formula to determine which cells to format.
  5. Under Edit the Rule Description, in the Format values where this formula is true list box, enter the following formula:

    =ISERROR(reference)

    Where reference is a relative reference to the cell that contains the error value.

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

  7. In the Color box, select white.

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

To do this task, use the IFERROR and NA functions.

Example

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

How 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 helpSelecting 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 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)
=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

Hide error values in a PivotTable report

  1. Click the PivotTable report.
  2. On the Options tab, in the PivotTable Options group, click the arrow next to Options, and then click Options.
  3. Click the Layout & Format tab, and then do one or more of the following:

    Change error display Select the For error values, show check box under Format. 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.

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. Click the Microsoft Office Button button image, click Excel Options, and then click the Formulas category.
  2. Under Error Checking, clear the Enable background error checking check box.