#286 Hiding Zeros in Excel

There are numerous reasons why you may not want to display zeros in your Excel spreadsheets. Here are three good ways you can hide them from view.

Hide Zeros for the entire worksheet:

If you do not want to display any zeros anywhere on entire sheet use this method. Note that you’ll need to do this for each sheet individually.

  1. From the File menu choose Options
  2. Go to the Advanced section listed at the left.
  3. Under the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
  4. Click OK.

Cell Formatting

If you only want to hide zero values in specific cells you can use a numeric format instead:

  1. After selecting the cells to format, from the Home tab click the Number group’s dialog launcher (the small arrow in the bottom-right corner.)
  2. Click the Number tab (if necessary).
  3. Choose Custom from the Category list.
  4. Enter 0;-0;;@ in the Type field.
  5. Click OK.

Conditional Formatting

There are a few exceptions for which the number formatting above may not work. In those cases you can hide zeros using conditional formatting.

  1. After selecting the cells you want to format, from the Home tab click the Conditional Formatting option in the Styles group.
  2. Choose New Rule.
  3. For the Rule Type select Format Only Cells That Contain
  4. For the operator drop-down, choose Equal To
  5. Enter a 0 (zero) for the value
  6. Then to hide the zero use the Formatbutton to make the font color white. (Note it’s still there the white just makes it invisible against the white background of the cells.)
  7. Click OK twice.

2 thoughts on “#286 Hiding Zeros in Excel

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