In worksheets it is
often preferable to have formulas that result in a calculation of zero
not display the zero but rather display an empty cell. This can be
accomplished in three ways. The first method suppresses all the zeros
in the worksheet. It is accomplished by removing the check from the
“Zero Values” check box in the Tools – Options – View dialog box.
The second method is
often more useful as it allows you to hide zero values in selected
cells. This is accomplished by placing a special “Custom Format” in
those cells where you do not want the zero to appear. The following
are the steps used to suppress zeros in selected cells:
- Select the cells
that contain the zeros (0's) you want to hide.
- On the Format
menu, click Cells, and then click the Number tab.
- In the Category
list, click Custom.
- In the Type box,
type 0;-0;;@
There is one
drawback to this method of suppressing zeros. 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. To get around this drawback
you can use the third method to hide zeros which is “Conditional
Formatting”. You can even have negative numbers formatted differently
than positive numbers. The following are the steps necessary to hide
zeros with the Conditional Formatting feature while at the same time
preserving the format you want for positive and negative numbers.
- Select the cell(s)
that you want to affect.
- Open the Format
Menu and choose Conditional Formatting.
- Select from the
dropdown lists the options necessary to build an equation equal to
zero.
- Next, click
Format.
- Click the dropdown
arrow to display the Color palette and select white (or the
appropriate background color for your worksheet)
- Click OK to return
to the Conditional Formatting dialog box.
- Click OK to accept
the settings you have made.
Using
the Conditional Format to Hide Zero Values
- END -
