Hiding the Display of Zero Values in Excel

In this article, we will learn how to hide or display all zero values in a worksheet in Microsoft Excel.

While preparing reports, you do not want to display zero values in a cell, or you are required to hide the zero values in the report.

img1

There are few ways to hide zeros in a cell.

  • Click on File ribbon
  • Select Excel Options

img2

  • Click on Advanced tab of the Excel Options
  • In Display options for this worksheet, Uncheck “Show a zero in cells that have zero value”

img3

  • Click on OK
  • Cell C3 contains zero, but this is not visible in the cell.
  • Although you can see the zero in the formula bar.

img4

The second method to hide zero values is using Custom Format

 

    • Select the cells that contain zero values that you want to hide
    • Click on Home ribbon
    • In Cells group, click on Format

>

  • Select Format Cells option or press shortcut key CTRL + 1

img5

  • The following dialog box will appear

img6

  • Select Number tab
  • Select Custom from Category menu
  • In the Type box, enter 0;-0;;@

img7

  • After that, click on OK
  • You will not be able to see zero in the cell whereas you can see the zero in formula bar.

Users are saying about us...

  1. I have a spreadsheet with multiple columns, specifically I am dealing with species of plants. The last column in the sheet gives the overall % each species occupies out of 100%. How do I leave the cells formatted and prevent 0% from showing up. The table is too confusing with all the 0%'s and it is not necessary for me to show 0%. Thanks for your time

  2. My worksheet hides all zeros that are supposed to be hidden except for one cell (although there are also a few cells that reflect zero that are intended to reflect zero). Because there are some cells in the worksheet that are intended to reflect 0.00, I cannot use the "Display Options" method to hide zeros. All cells that are intended to have hidden zeros have been custom formatted as: 0.00;-0.00;;@, but the one defiant cell still reflects a visible 0.00. Other cells in the same column and row do not do this. The formulas in the cells immediately adjacent to (and including) the delinquent cell are all correct, so it has to be something with the formatting. I have tried changing the custom formatting for that cell to: 0;-0;;@ as well as ;;; but the cell still reflects a visible 0.00. I have also tried conditionally formatting that individual cell to change the font to the background color for the cell (white) if the cell value equals 0, but it STILL reflects a visible (black) 0.00.

    The cell is AF20. The formula is =IF(AND(I200,AS19>8-I20,AI20=0,AL20=0,AM20=0),8-I20,IF(AND(I20<=8,AS19<8-I20,AI20=0,AL20=0,AM20=0),AS19,0)). I am an excel novice and I'm out of ideas. I have tried researching solutions, but the only solutions I have found are things I have already tried. Any suggestions?

    • Hey APSmith,

      I think I understood your issue. Conditional formatting is not the solution to your problem. The problem lays in the custom formatting cell option. Use the character wisely checking the keyboard settings before typing. But it's not a worrying thing. Try the Custom formatting option again. Excel may sometimes surprise you with its tricky results. Do let us know more about the problem after trying again.
      Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube