Display or hide zero values
Hide or display all zero values on a worksheet
-
Go to File > Options > Advanced.
Options
-
Under Display options for this worksheet, select a worksheet, and then do one of the following:
-
To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.
Zero Value
-
To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.
Zero Value Check Box
Hide zero values in selected cells
These steps hide zero values in selected cells by using a number format. The hidden values appear only in the formula bar and are not printed. If the value in one of these cells changes to a nonzero value, the value is displayed in the cell, and the format of the value will be similar to the general number format.
-
Select the cells that contain the zero (0) values that you want to hide.
Zero Values to hide
-
You can press Ctrl+1, or on the Home tab, select Format > Format Cells.
Format Cells
-
Select Number > Custom.
Custom
In the Type box, choose 0, and then select OK.
Type Box
Display hidden values
To display hidden values:
-
Select the cells with hidden zeros.
Select Hidden Zeros
-
You can press Ctrl+1, or on the Home tab, select Format > Format Cells.
Format Cells
-
Select Number > General to apply the default number format, and then select OK.
Number General
Hide zero values returned by a formula
-
Select the cell that contains the zero (0) value.
Zero Value
-
On the Home tab, select the arrow next to Conditional Formatting > Highlight Cells Rules > Equal To.
Conditional Formatting
In the box on the left, type 0.
Type 0 in the box
-
In the box on the right, select Custom Format.
Custom Format
In the Format Cells box, select the Font tab.
Format Cells: Font Tab
-
In the Color box, select white, and then select OK.
Color Box