• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Show zeros as blanks in Excel

by on
in Excel Training,Office Technology

Q. How do I make a 0 show up as a blank on the screen, but still have the value 0 in the cell? I need the zero for other formulas.

A. There are two ways to do this. First, in Options (File Options, Advanced in Office 2010-13 and Office Button, Excel Options in 2007), choose the Advanced category. Scroll down until you see “Display options for this worksheet.” Click the checkbox that says “Show a zero in cells that have a zero value” to clear the check mark. It is checked by default. This option is set for a particular worksheet, not the whole workbook or any other workbooks. You’ll still see the zero in the Formula bar or in edit mode.  

If you don’t want to apply this op­­tion to the whole worksheet, you can use a Conditional Format. Select the cells where you want to apply Conditional Formatting. In the Conditional Formatting dropdown menu, choose Highlight Cells Rules and Equal to…. Type a 0 in Format cells… on the left and choose Custom Format from the dropdown field on the right. On the Number tab, choose the bottom option, Custom and replace the word General under the word Type: on the right with two semicolons (;;). This makes any number in that cell appear the same color as the fill color, regardless of the fill color.

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/36254/show-zeros-as-blanks-in-excel "

Leave a Comment