Display or hide zero values

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Try Office 2010 In Excel 2010, you can use the IFERROR function to display or hide zero values in your worksheet.
Read an article or try Office 2010!

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. On the Tools menu, click Options, and then click the View tab.
  2. Do one of the following:
    • To display zero (0) values in cells, select the Zero values check box.
    • To display zero values as blank cells, clear the check box.

Top of Page Top of Page

Use a number format to hide zero values in selected cells

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 Format menu, click Cells, and then click the Number tab.
  3. In the Category list, click Custom.
  4. In the Type box, type 0;-0;;@


 Notes 

  • The hidden values appear only in the formula bar Formula bar — or in the cell if you edit within the cell — and are not printed.
  • To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Top of Page Top of Page

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 Format menu, click Conditional Formatting.
  3. In the box on the left, click Cell Value Is.
  4. In the second box from the left, click equal to.
  5. In the box on the right, type 0.
  6. Click Format, and then click the Font tab.
  7. In the Color box, select white.

Top of Page Top of Page

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.
  • 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. In the worksheet, select cell A1, and press CTRL+V.
  2. 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

IF

Top of Page Top of Page

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

 
 
Applies to:
Excel 2003