Software

Pro tip: Three ways to hide zero values in an Excel sheet

Although zero values are valid, you might need to hide them. Here are three methods for hiding zero values in an Excel sheet.

excel-logo.png
Zeroes are valid data, but for various reasons you won't always want to display them in an Excel sheet. When this is the case, you have at least three options. Your data and business requirements will dictate the method you choose.

Sheet setting

Perhaps the simplest way to hide all zero values in a sheet is to let the worksheet do it. Just remember, this setting works at the sheet level. You must set it for each sheet in the workbook, as follows:

  1. Click the File menu and then choose Options (under Help). 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 Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
  4. Click OK.

Numeric format

The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, 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 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 (if necessary).
  4. Choose Custom from the Category list.
  5. Enter 0;-0;;@ in the Type field.
  6. Click OK.

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.

Conditional format

The numeric format shown above will hide literal zeroes and most returned by a formula. When you run into an exception, you can use a conditional format as follows:

  1. Select the cells that contain the 0s you want to hide.
  2. Click the Home tab and then click the Conditional Formatting option in the Styles group. Choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 4.
  3. In the top pane, select the Format Only Cells That Contain option.
  4. From the second dropdown, choose Equal To.
  5. Enter 0 in the third control.
  6. Click Format.
  7. From the Color dropdown, choose White (or the color that matches the sheet's background).
  8. Click OK twice.

This format is easy to forget and can cause trouble down the road, so choose this method carefully.

Best choice

Of the three methods, the conditional formatting method is probably the least productive because of the potential to forget it; costs can run high with employing formatting tricks. The sheet and numeric format methods are more straightforward and require little effort.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
tusty
tusty

 how about if it's a formula that an IF statement is used that if it calculates to zero then null is displayed else it displays the results 

 for example

 =IF(a+b-c=0,"",a+b-c)
jharvie673
jharvie673

How about changing text color to white?

chetfreeman
chetfreeman

There's a fourth way, but iy's only good for a cell or range of cells.  Go to the cell, hold down CTRL and hit "1."  Pick custom.  Put three semi-colons in the custom box.  This hides everything.   Copy this format to any other cell with a zero.  Cumbersome but it works.

sparent
sparent

Finally able to get away from conditional formatting! The sheet setting is a bit overwhelming. The number format is definitely the best option. Thanks, Susan.

CliveAH
CliveAH

I would like to find an excel program default that doesn't display zeroes, so that one had the choice of displaying only zeroes that were needed to be shown. I wonder, is this available - if so, it's well hidden?

MarkSmith2206
MarkSmith2206

Hey. Thanks. These are great tips. It will be really useful to me as I have in process of learning excel to its depth ​<a href=http://www.pmstudy.com>pmp training</a>

cgstms
cgstms

The only problem with this one is that the cell contents would not be treated as a number and that would impact on the results of statistical functions.

Colin Small