You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values. There are several ways to display or hide zero values.What do you want to do?

Display or hide all zero values on a worksheet

  1. Click the Microsoft Office Button button image, click Excel Options, and then click the Advanced category.
  2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

    • To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

    • To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

Use a number format to hide zero values in selected cells

Caution Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

  1. Select the cells that contain the zero (0) values that you want to hide.
  2. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  3. In the Category list, click Custom.
  4. In the Type box, type 0;-0;;@

Notes:

Use a conditional format to hide zero values returned by a formula

  1. Select the cell that contains the zero (0) value.
  2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Equal To.
  3. In the box on the left, type 0.
  4. In the box on the right, select Custom Format.
  5. In the Format Cells dialog box, click the Font tab.

  6. In the Color box, select white.

Use a formula to display zeros as blanks or dashes

To do this task, use the IF function.

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
10
Formula Description (Result)
=A2-A3 Second number subtracted from the first (0)
=IF(A2-A3=0,"",A2-A3) Returns a blank cell when the value is zero (blank cell)
=IF(A2-A3=0,"-",A2-A3) Returns a dash when the value is zero (-)

Function details

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