Display or hide zero values

If your sheet contains zero values or contains calculations that produce zero values, you can hide the values or use formatting options to change how the values will display. Excel automatically applies the general or number format to any number you enter or paste into a worksheet. These formats automatically remove leading zeros from numbers. If you want to keep leading zeros, you must create a custom number format. For more information about how to keep leading zeros, see Keep leading zeros.

Learn about several options for displaying or hiding zero values in the sections below.

Hide all zero values on a sheet

Follow these steps:

On the Excel menu, click Preferences.
Under Authoring, click View .
Under Window options, uncheck the Show zero values box.

Hide zero values in selected cells

Caution: This option applies only to zero (0) values in cells. If a zero value in one of the selected cells changes to a nonzero value, Excel uses the General number format to display the value.

Follow these steps:

Select the cells that contain the zero (0) values that you want to hide.
On the Home tab, under Number, on the Number Format pop-up menu , click Custom.
In the Format Cells dialog box, under Category, click Custom.
In the Type box, type 0;-0;;@, and then click OK.

Note: The hidden values appear only in the formula bar — or in the cell if you edit the contents directly in the cell — and are not printed when you print the sheet. If you are editing directly in the cell, the hidden values are displayed in the cell only while you are editing.

Display hidden zero values in selected cells

Follow these steps:

Select the cells that contain the hidden zero (0) values that you want to display.
On the Home tab, in Number, on the Number Format pop-up menu , click Custom.
In the Format Cells popup window, in the Category list, do one of the following:

To	Click
Display hidden values by using the standard number format	General.
Display hidden values by using a date or a time format	Date or Time, and then select the date or time format that you want to use.

Hide zero values that are returned by a formula

Follow these steps:

Select the cells that contain a zero (0) value.
On the Home tab, in Format, click Conditional Formatting.
Click New Rule.
On the Style pop-up menu, click Classic, and then on the Format only top or bottom ranked values pop-up menu, click Format only cells that contain.
On the Specific text pop-up menu, click Cell value.
On the between pop-up menu, click equal to, and then in the box next to equal to, type 0.
In the Format with pop-up menu, click custom format, and then on the Color pop-up menu, click White.

Display zero values as blank cells or dashes

You can use the IF function to specify a condition that displays a zero (0) value as a blank cell or as a dash (-) in a cell. To learn more, see the IF function article.

Follow these steps:

On a blank sheet, in cell A1 and cell A2, type 10.
In cell A3, type =A1-A2, and then press RETURN .

The formula returns a zero (0) value.
On the Formulas tab, in Function, click Formula Builder.
In the Formula Builder list, double-click IF.
Under Arguments, click the box next to value1 and type A3.
Under Arguments, click the word True next to is, and then on the pop-up menu, click = (Equal To).
Under Arguments, click the box next to value2 and type 0.
Under Arguments, click the box next to then and do one of the following:

To display zero values as	Type this
Blank cells	""
Dashes	"-"

Click the box next to else and type A3, then press the Enter key.

The formula in cell A3 returns a zero (0) value, and Excel displays the value located between the quotation marks in the then argument (also called the value_if_true argument).

Tip: You can put any text that you want to display for zero values in between the quotation marks.

Hide zero values in a PivotTable report

Follow these steps:

Click the PivotTable report.
On the PivotTable tab, under Data, click Options.
On the Display tab, do one or more of the following:

To	Do this
Display a certain value in place of an error code	Check the Error values as box, and then in the field, type the value that you want to display instead of an error code. To display errors as blank cells, leave the box empty.
Display a certain value in place of an empty cell	Select the Empty cells as check box, and then in the box, type the value that you want to display in empty cells. To display blank cells, leave the box empty. To display zeros, clear the Empty cells as check box.

Do this

Display a certain value in place of an error code

Check the Error values as box, and then in the field, type the value that you want to display instead of an error code.

To display errors as blank cells, leave the box empty.

Display a certain value in place of an empty cell

Select the Empty cells as check box, and then in the box, type the value that you want to display in empty cells.

To display blank cells, leave the box empty.

To display zeros, clear the Empty cells as check box.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

Microsoft Office Tutorials

Friday, May 25, 2018