Q: How do I keep zeroes from displaying in my Excel worksheet?
A: Zeroes are valid data, but for various reasons you may not want to display them in an Excel sheet. When this is the case, you have three options. Your data and business requirements will dictate which option you need, as follows:
The simplest way to hide all zero values is to let the worksheet do the work. Remember that this setting works at the sheet level, so you must do this for each sheet in the workbook:
1. Click the File menu and then choose Options. In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Choose Advanced in the left pane. In Excel 2003, click the View tab.
3. In the section called Display Options For This Worksheet, uncheck the Show a zero in cells that have a zero value.
4. Click OK.
The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all of them, you can use a numeric format instead:
1. Select the cells that contain the zero values that you want to hide.
2. Click the Home tab and then click the Number group's dialog launcher (the small arrow in the bottom-right corner). In Excel 2003, choose Cells from the Format menu.
3. Click the Number tab.
4. Choose Custom from the Category list.
5. Overwrite what is already written in the TYPE: box by typing 0;-0;;@
6. Click OK.
Hidden 0s will still be visible in the Formula bar or in the cell, if you double-click the cell. To undo this format, simply choose an alternate numeric format for the cells.
The numeric format shown above will hide literal zeroes and most zeroes that are a result of a formula or function. When you run into an exception to this, you can use a conditional format as follows:
1. Select the cells that contain the zeroes you want to hide.
2. Click the Home tab and then click the Conditional Formatting option in the Styles group.
3. Choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 5.
4. In the top pane, select the Format Only Cells That Contain option.
5. From the second dropdown, choose Equal To.
6. Enter 0 in the third control.
7. Click Format.
8. From the Color dropdown, choose White (or the color that matches the sheet's background).
9. Click OK twice.
This format is easy to forget and can cause trouble down the road, so choose this method carefully.