• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Show zeros as blanks in Excel

Get PDF file

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.

Leave a Comment