Option #1: Sheet setting
The easiest way to hide all zero values in a worksheet is to change the setting for the sheet, as follows:
- Click the File menu and then choose Options (under Help).
( In Excel 2003, choose Options from the Tools menu.)
- Choose Advanced in the left pane.
(In Excel 2003, click the View tab.)
- In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
- Click the OK button.
Option #2: Numeric format
If you don’t want to hide all the zero values in the sheet, you can use a numeric format to hide specific cells with zero values:
- Select the cells that contain the zero values that you want to hide.
- Click the Home tab and click the Number group’s dialog launcher (the small arrow in the bottom-right corner. In Excel 2003, choose Cells from the Format menu.
- From the Number tab choose Custom from the Category list.
- Enter 0;-0;;@ in the Type field.
- Click OK.
NOTE: Hidden 0s will still be visible in the Formula bar, or in the cell, if you edit in the cell. To undo this format, simply choose an alternate numeric format for the cells.
Option #3: Conditional formatting
The numeric format above will hide literal zeroes and most of the zero results of a formula. For the exceptions, you can use conditional formatting as follows:
- Select the cells that contain the zeros you want to hide.
- From the Home tab click the Conditional Formatting option in the Styles group and then choose New Rule...
(In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 4.)
- In the top pane, select the Format Only Cells That Contain option.
- From the second dropdown, choose Equal To.
- Enter 0 in the third control.
- Click Format.
- From the Color dropdown, choose White (or the color that matches the sheet’s background).
- Click OK twice.
WARNING! This format is easy to forget and can cause trouble down the road, so be very careful if you go this route.
Do you ever need to hide zero values? Let us know how you handle them.