How to hide zero values in an Excel worksheet

If a worksheet contains rows upon rows of numbers, things can look quite cluttered.

As you can see in the example above, the large number of zero values draws the eye away from the fields that contain actual data.

One of the tricks to make the worksheet look less chaotic is by simply not showing the zero values. There are several ways to do this in Excel.

Conditional formatting

Most users would use theConditional formatting option. There’s nothing wrong with this approach, but it does have some disadvantages:

• You can use conditional formatting to change the text colour. The problem is that this formatting won’t take the background colour of cells into account. If you’d take the example above and set the formatting so that the text for all zero values becomes white, you won’t see any zeroes. White characters on a white background become invisible. But if the background colour were changed to dark green, the white 0 would be visible again.
• If you add new columns to the table, you’ll need to extend the conditional formatting to include the new columns.

Number formatting

When people hear the words ‘number formatting’, they usually think of date formatting, displaying numbers with one or more decimals, using scientific formatting to display values, …

But you can also use number formatting to indicate that the value 0 shouldn’t be displayed. The difference with conditional formatting is that this method still works if the cell’s background colour is changed.

Just follow these steps:

• Select the area where you want to hide the zero values.
• Right-click your selection and go to Format Cells – Custom.
• Enter the following code: 0;[Red]-0;

This code consists of three parts (divided by a semicolon) which refer to positive, negative, and zero values.

• 0: The first zero means positive values should be shown without decimals.
• [Red][Red]-0: This means negative values should be displayed in red and preceded by a minus sign.
• There’s nothing after the last semicolon. This last part defines how you want zero values to be displayed. If you don’t define anything here, you won’t see these values.

Note: If you had used the code 0;[[Red]]-0;[[Green]]”Zero”, all zero values would’ve been visually replaced by the green word ‘Zero’.

Excel settings

The easiest and most flexible way to do this is by changing Excel’s settings.

You can do this by going to File – Options – Advanced. Next, untick the option Show a zero in cells that have zero value (you’ll find this option in the group Display options for this worksheet).

The result is a much clearer-looking worksheet in which you immediately see the cells that contain ‘real’ values.

You can add rows and columns to the worksheet and enter values outside of the table – whatever you do, you won’t see a zero value. In other words: this option applies to the entire worksheet(not the workbook)! If you want to activate this for several worksheets, you’ll need to do it for each worksheet individually.