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