#325 Hiding Zeros in Excel

There are a variety of reasons you may not want to show zeros in your spreadsheets. If you ever want to hide cells with zero values, here are three alternatives for you to consider.

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:

  1. Click the File menu and then choose Options (under Help).
    ( 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 the OK button.

Worksheet-Setting

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:

  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.Number-DialogBox
  3. From the Number tab choose Custom from the Category list.
  4. Enter 0;-0;;@ in the Type field.
  5. Click OK.

Formatting

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:

  1. Select the cells that contain the zeros you want to hide.
  2. 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.)
  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.

ConditionalFormattingBox

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.

Advertisements

One thought on “#325 Hiding Zeros in Excel

  1. Nice post. The only time I want to remove zeros, is when I’m creating a graph and I don’t want the lines to drop to the zero line and instead break up. I usually use formulas to display #N/A instead of the zero and get rid of them that way, but I’m going to try your option 2, it might be simpler. Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s