For those who use Microsoft Excel, there are several ways to display 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. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.
Sometimes it requires you to add 0 before some numbers, even though 0 before any number won’t make any sense, but in some cases, your sheet may look neater, more understandable, and gives you more clarity. You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that sticks to a set of format standards that requires you to hide zero values. So here’s a simple trick to display or hide 0 in the cell.
How to show or hide zero values in a Microsoft Excel sheet
- Step 1: Launch Microsoft Excel on your computer and head to the menubar, click File -> Options -> Advanced as shown.
- Step 2: Under Display options for this worksheet, select a worksheet, and then choose one of the following:
- To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.
- To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.
This will hide the zeros in the entire worksheet, but if you want to show or hide zero values in a specific cell, there’s a different method. Take a look at it below.
How to Show or hide zero values in a selected cell
If you don’t want the entire sheet to show or hide zeroes, you can use these steps to show/hide zero values in selected cells by using a number format.
Hide zero values in the cell
- Step 1: Select the cells that contain the zero (0) values that you want to hide.
- Step 2: Press Ctrl+1 or go to Format -> Format Cells on the Home tab.
- Step 3: Then go to Number -> Custom as shown.
- Step 4: In the Type box, type 0;-0;;@, and then click OK.
Show zero values in the cell
- Step 1: Select the cells with hidden zeros.
- Step 2: Press Ctrl+1, or go to Format -> Format Cells on the Home tab.
- Step 3: Now head to the Number -> General to set the default number format, and then click OK.
The hidden values appear only in the formula bar and are not printed. If the value in one of these cells changes to a nonzero 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’s a simple trick that you can use, all you need to do is type in ‘ (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 was it, there are several tricks, quick shortcuts, hacks, and time-saving workarounds in Microsoft Excel, you can view our How-To Guides section for more. Here are some of these useful tips below, check them out.
- How To Hide Formulas In Microsoft Excel
- How to add a drop-down list in Microsoft Excel
- How To Create Your Own Shortcuts In Microsoft Excel
- How To Hide Cell Data In Microsoft Excel
To get updates on the latest tech news, smartphones, and gadgets, make sure you follow us on our social media profiles.
