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.


There are few ways to hide zeros in a cell.

  • Click on File ribbon
  • Select Excel Options


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


  • 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.


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


  • The following dialog box will appear


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


  • 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.

3 thoughts on “Hiding the Display of Zero Values in Excel

  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>