To hide zeros in selected cells, press Ctrl + 1, choose Custom and enter #,##0.00;-#,##0.00;;@. The third section, left empty, controls zeros, so they show as blank while other numbers keep their format.
To show a dash instead of a blank, use #,##0.00;-#,##0.00;"-";@. To hide zeros across a whole sheet, go to File → Options → Advanced → Display options for this worksheet and untick Show a zero in cells that have zero value.
Match the first two sections to your data rather than using 0;-0;;@ as it is. That version also rounds 1,234.56 to 1235 and drops the thousands separator.
Hiding changes the display only. The zero is still in the cell, in the formula bar and in any chart. Blanking zeros with a formula such as =IF(B2=0,"",B2) returns empty text instead, and any formula that adds or multiplies that cell returns #VALUE!.
Avoid conditional formatting with a white font. The zeros reappear on any shaded row or filled cell. If you use conditional formatting, set the rule’s Number format to the custom code ;;; instead.
In Excel, users can hide the zero values in the data set and make the cells look blank if they do not want to display the zero values in their data set.
And they can do it within the selected cells range only or in the entire worksheet as Excel has multiple ways to hide the zero values.
In this tutorial, we have explained to you the multiple ways to hide the zero values
Steps to Hide Zeros in the Worksheet Using Excel Options
Excel has a built-in feature that can be used to hide all the zero values automatically within the entire worksheet using the below steps.
- First, open the workbook and then go to the File tab.
- After that, click on Options.
- Now, click on “Advanced” from the left pane and then scroll down to the “Display options for this worksheet”.
- From here, click on the drop-down icon and select the worksheet in which you want to hide all the zero values.
- In the end, uncheck the “Show a zero in cells that have zero value” and click Ok.
- Once you click OK, all the zero values in the worksheet will get hidden and those cells will look blank.
Now whenever you will enter the zero value anywhere in this selected worksheet, Excel will hide that zero value.
Hide Zero Values in the Selected Cells Using Format Cells
- First, select the cells or the data range from where you want to hide the zero values.
- After that right click with the mouse and click on the “Format Cells” option and you will get the “Format Cells” dialog box opened.
- Now, under the “Number” tab click on the “Custom” option and then enter the custom type as 0;-0;;@ and click OK.
- Once you click OK, all the zero values within the selected range will get hidden.
The above-used custom formatting string “0;-0;;@” consists of four parts.
The first 0 is for positive numbers to display the positive values as is, the 2nd part -0 is for negative numbers to display the negative values as is, the 3rd part 0 is missing which is to hide the zero values, and the 4th part @ is to display the text as is: <Positive Numbers>;<Negative Numbers>;<Zeros>;<Text>
Hide Zeros in the Selected Cells Using Conditional Formatting
- First, select the cells or the data range from where you want to hide the zero values and go to the “Home” tab and then click on the “Conditional Formatting” icon.
- After that click on the “Highlight Cells Rules” option and then on the “Equal To” option.
- Now, within the “Equal To” dialog box in the left side field, enter (0) zero and then click on the drop-down on the right side and choose “Custom Format”.
- From here, you will get the “Format Cells” dialog box opened, and under the “Font” tab choose the white color and click OK.
- Once you click OK, the font color of the cells that contains zero value will become white, which will make the cells look empty or blank.
Hiding zero (0) values only mean hiding the value in the cells not removing them, so after hiding the value, cells contain value even if they look blank.