Skip to main content

how to hide 0 values in a spreadsheet?

option 1: 
Perhaps the simplest way to hide all zero values in a sheet is to let the worksheet do it Just remember, this setting works at the sheet level. You must set it for each sheet in the workbook, as follows.

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

option 2 :
The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, you can use a numeric format instead:

  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.
  3. Click the Number tab (if necessary).
  4.  Choose Custom from the Category list.
  5. Enter 0;-0;;@ in the Type field.
  6. Click OK.

  * The hidden values appear only in the formula bar Formula bar  or in the cell if you edit within the cell and are not printed.
  * To display hidden values again, select the cells, click the "Cells" command on the "Format" menu, and then click the "Number" tab. In the "Category" list, click "General" to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the "Number" tab.
Optipn 3 : 
Use a conditional format to hide zero values returned by a formula

  1. Select the cell that contains the zero (0) value.
  2. On the "Format" menu, click "Conditional Formatting".
  3. In the box on the left, click "Cell Value Is".
  4. In the second box from the left, click "equal to".
  5. In the box on the right, type 0.
  6.   Click "Format", and then click the "Font" tab.
  7. In the "Color" box, select white.
  8. Click ok.

Follow Something of Everything.