"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Preventing the display of zeros

There may be times when you don't want a report full of zeros. Maybe the report would be easier to read if only non-zero values were shown. It was only last week that someone asked me how to do this.

One option is to select Tools, Options and select the View tab. Then deselect the 'Zero values' option. If you want to hide zero values for only part of your worksheet, the following stpes will do the trick.

To easily format a range of cells to hide zero values:

  1. Select the range of cells you want to apply the format to.
  2. Select Format, Cells and click on the Number tab.
  3. In the Category box, select Custom. The current format (i.e. #,##0.00;-#,##0.00) should be displayed in the Type field. Notice that there's a semi-colon in the format. The format to the left of the semi-colon is for positive numbers. The format to the right of the semi-colon is for negative numbers.
  4. A little-known trick is that you can add a second semi-colon to the right of the existing format (i.e. #,##0.00;-#,##0.00;). This will force cells containing zero values to display as blanks.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)


If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.