How to show or hide zero values in a Microsoft Excel sheet

For those who use Microsoft Excel, there are several ways to show or hide zero values. There are times when you don’t want to show zero (0) values ​​on your worksheets, sometimes you need them to be seen. Regardless of whether your format standards or preferences require zeros to be displayed or hidden, there are several ways to make this happen.

Sometimes it requires you to add 0 before some numbers, even if 0 before any number doesn’t make any sense, but in some cases your sheet can look neater, more understandable and gives you more clarity. You may have a personal preference for displaying zero values ​​in a cell or you may be using a spreadsheet that adheres to a number of format standards that require you to hide zero values. So here’s a simple trick to show or hide 0 in cell.

How to add a drop down list in Microsoft Excel

How to show or hide zero values ​​in a Microsoft Excel sheet

  • Step 1: Launch Microsoft Excel on your computer and go to the menu bar, click File -> Options -> Advanced as shown.
  • Step 2: Under View the options for this worksheetselect a worksheet, then choose one of the following options:
  1. To display zero (0) values ​​in cells, check the Show zero in cells that have zero value check box.
  2. To display zero (0) values ​​as blank cells, clear the Show zero in cells that have zero value check box.

This will hide the zeros in the whole worksheet, but if you want to show or hide the zero values ​​in a specific cell, there is a different method. Take a look below.

How to show or hide zero values ​​in a selected cell

If you don’t want the whole sheet to show or hide zeros, you can use these steps to show / hide zero values ​​in selected cells using a number format.

Hide zero values ​​in the cell

  • Step 1: Select the cells containing the zero (0) values ​​you want to hide.
  • Step 2: to press CTRL + 1 or go to Format -> Format cells on Home form.
  • Step 3: Then go to Number -> Custom as shown.
  • Step 4: In the Type box, type 0; -0 ;; @then click OK.


Show zero values ​​in the cell

  • Step 1: Select cells with hidden zeros.
  • Step 2: to press CTRL + 1or go to Format -> Format Cells on Home form.
  • Step 3: Now go to Number -> General to set the default number format, then click OK.

Hidden values ​​are displayed only in the formula bar and are not printed. If the value in any of these cells changes to a non-zero value, the value will be displayed in the cell and the format of the value will be similar to the general number format.


If you want to add a zero (0) before any number, there is a simple trick you can use, all you need to do is type (single quote) before your zeros when you enter them in the cell. For example, type ‘0001 to add this number 0001 in your sheet

That’s it, there are several tricks, quick shortcuts, hacks and workarounds to save time in Microsoft Excel, you can view our How-to Guides section for more information. Here are some of these helpful tips below, check them out.

To receive updates on the latest tech news, smartphones and gadgets, be sure to follow us on our social media profiles.

Instagram | Facebook | Twitter | Youtube

Google News button

Leave a Reply

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