Stop Zeroes Displaying

Is it possible to stop zero values displaying in a range?

By Neale Blackwood

That can be achieved using a Custom Number format. First select the range, then click the Format menu and select Cells (Ctrl + 1 will open the Format Cells dialog box). Click the Number tab and select Custom from the list. Enter the following format in the Type box:

#,##0;#,##0;

This Custom Number format will not display zeroes. It has zero decimal places and uses the comma separator for thousands.

The first selection of the Custom Number format is for the positive number format. A semi-colon is used to separate the different sections of the format. The second section is for the negative number format. The third section is for the zero format. Because there is nothing after the last semi-colon, nothing will display for a zero value. You can see examples of other number formats by scrolling down the Custom format list.

Extras

In Excel 2003 and earlier versions you can also go to theTools menu, under Options in the View tab you can deselect the Zero Values checkbox to stop zeroes displaying in the whole sheet.
In Excel 2007 this is in the Round Office button in Excel Options under the Advanced section under Display options for this worksheet.

The Format dialog can be accessed via the Home Ribbbon tab in Excel 2007.