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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube