How to Automatically Hide Zero Values In Cells

In this method, we will use the options provided by Excel to hide zero values in the cells. This method hides the zero value within the complete current worksheet.

Step 1: Open your Excel Sheet and Go to File Tab

Open your dataset sheet and click on the File tab.

Step 2: Select Options Menu in Side Menu Bar

Once we click the File tab, excel will open the side menu bar. We need to go to the Options menu.

Step 3: Select Advance

After we click on the Options menu, a popup will come we need to go to the Advance option.

Step 4: Go to Display Options for this Worksheet and Uncheck Show Row and Columns Headers

In the Advanced options tab, scroll down and go to Display options for this sheet (Here, it is Sheet 4) and uncheck the check box for Show a zero in cells that have zero value and click the OK button.

Step 5: Click Ok and Check that Values are Hidden

Once we click on the OK button in the Advanced tab option, excel will automatically hide the zero value in the current working sheet. This change will be also applied to all those cells which zero value as the result of some formula.

Note: This method only hides the zero value, still zero is present in the cell, But it’s hidden.

Microsoft Excel enables us to format, organize and calculate data in a spreadsheet. It’s a great tool for preparing datasets and it makes those datasets easier for users and analysts to analyze the data. Sometimes these datasets contain zero values that are not required to be seen. Excel provides an easy way to hide those zero values without affecting the data and the datasets. Whenever we have a large dataset, many of the data values in the dataset may be zero and as per requirement, we may need to hide those zero values and leave the cells empty. Let’s learn how we can Hide Zero Values In Excel.

Hiding Zero Values in Excel

Let’s assume we have the following dataset, showing the sales of different courses in different cities.

We can see that our dataset contains zero(0) elements that we need to remove. This can be easily done by removing each 0 entry one by one for such a small dataset. But If we have a massive dataset with thousands of entries it became very difficult to remove elements manually.