Hide error values and error indicators

If your formulas have errors that you anticipate and don't need to correct, you can improve the display of your results by hiding error values and error indicators in cells.

Formulas can return errors for many reasons. For example, Excel cannot divide by 0, 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!. Cells with error indicators, which appear as triangles in the top-left corner of a cell, contain formula errors.

Hide error indicators in cells

If a cell contains a formula that breaks a rule that Excel uses to check for problems, a triangle appears in the top-left corner of the cell. You can prevent these indicators from displaying.

Cell flagged with an error indicator

Cell error indicator

  1. On the Excel menu, click Preferences.

  2. Under Formulas and Lists, click Error Checking  Error checking icon , and then clear the Enable background error checking check box.

    Tip: You can also hide precedent and dependent tracer arrows once you've identified the cell that is causing an error to appear. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

More options

  1. Select the cell that contains 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)

  3. Do one of the following:

    To display

    Do this

    A hyphen when the value is an error

    Type a hyphen (-) inside the quotation marks in the formula.

    NA when the value is an error

    Type NA inside the quotation marks in the formula.

    #N/A when the value is an error

    Replace the quotation marks in the formula with NA().

  1. Click the report.

  2. On the PivotTable Analyze tab, click Options.

    On the PivotTable Analyze tab, select Options

  3. On the Display tab, select the Error values as check box, and then do one or more of the following:

    To display

    Do this

    A value in place of error values

    Type the value that you want to display instead of errors.

    A blank cell in place of error values

    Delete any characters in the box.

Tip: You can also hide precedent and dependent tracer arrows once you've identified the cell that is causing an error to appear. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

  1. Click the report.

  2. On the PivotTable Analyze tab, click Options.

    On the PivotTable Analyze tab, select Options

  3. On the Display tab, select the Empty cells as check box, and then do one or more of the following:

    To display

    Do this

    A value in empty cells

    Type the value that you want to display in empty cells.

    A blank cell in empty cells

    Delete any characters in the box.

    A zero in empty cells

    Clear the Empty cells as check box.

Hide error indicators in cells

If a cell contains a formula that breaks a rule that Excel uses to check for problems, a triangle appears in the top-left corner of the cell. You can prevent these indicators from displaying.

Cell flagged with an error indicator

Cell error indicator

  1. On the Excel menu, click Preferences.

  2. Under Formulas and Lists, click Error Checking  Excel Error Checking Preferences button , and then clear the Enable background error checking check box.

    Tip: You can also hide precedent and dependent tracer arrows once you've identified the cell that is causing an error to appear. On the Formulas tab, under Audit Formulas, click Remove Arrows  Remove All Arrows button .

More options

  1. Select the cell that contains 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)

  3. Do one of the following:

    To display

    Do this

    A hyphen when the value is an error

    Type a hyphen (-) inside the quotation marks in the formula.

    NA when the value is an error

    Type NA inside the quotation marks in the formula.

    #N/A when the value is an error

    Replace the quotation marks in the formula with NA().

  1. Click the report.

  2. On the PivotTable tab, under Data, click Options.

    PivotTable tab, Data group

  3. On the Display tab, select the Error values as check box, and then do one or more of the following:

    To display

    Do this

    A value in place of error values

    Type the value that you want to display instead of errors.

    A blank cell in place of error values

    Delete any characters in the box.

Note: You can also hide precedent and dependent trace arrows once you've identified the cell that is causing an error to appear. On the Formulas tab, under Audit Formulas, click Remove Arrows  Remove All Arrows button .

  1. Click the report.

  2. On the PivotTable tab, under Data, click Options.

    PivotTable tab, Data group

  3. On the Display tab, select the Empty cells as check box, and then do one or more of the following:

    To display

    Do this

    A value in empty cells

    Type the value that you want to display in empty cells.

    A blank cell in empty cells

    Delete any characters in the box.

    A zero in empty cells

    Clear the Empty cells as check box.

See also

Remove or allow a circular reference

Add error bars or up/down bars to a chart

Correct an inconsistent formula

Display or hide zero values

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×