Skip to content
Related Articles

Related Articles

How to Hide the Zero Values in Excel?

View Discussion
Improve Article
Save Article
  • Last Updated : 02 Jun, 2022
View Discussion
Improve Article
Save Article

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 which 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, which will show the sales of different courses in different cities.

Dataset

Fig 1 – Dataset

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.

Method 1: 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 By Step Implementation

Step 1: Open your dataset sheet and click on the File tab.

Clicking-file-tab

Fig 2 – File Tab

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

Open-options-menu

Fig 2 – Option Menu

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

Clicking-advanced-option

Fig 3 – Advance Option

Step 4: In the Advanced option 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.

Options-for-current-worksheet

Fig 4 – Display options for the current worksheet

Step 5: Once we click on the OK button in 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.

Zeros-hidden

Fig 5 – Output

Method 2: Using Conditional Formatting Hide Zero Value In Cells

In this method, we will be using the Conditional Formatting option and set the rule to hide the zero value cells in the worksheet. This method is really useful when we want to hide zero values within a specific range. Also, using this method not only we can hide the zero value cell but we can also highlight the cells which are having zero value.

Step By Step Implementation

Step 1: Open your dataset sheet and go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Equal To and click on the Equal To option.

Conditional-formatting

 

Step 2: Once we click on the Equal To option, it will open a popup asking how we want our zero value cells.

Formatting-cells

 

In the above picture, we are formatting cells equal to zero with Green color. This formatting rule will fill the zero value cells with green color in the selected dataset. After setting the rules we need to click on the OK button.

Rule-selected

Fig 8 – Output

It also gives flexibility to completely make the cells blank by using a custom white color. In the next steps, we can see how we can do that. For this, we need to repeat the first two steps (Step 1 & Step 2) and In step 2 we need to select the Custom Format option.

Custom-format

Fig 9 – Custom Format Option

Once we click on the OK button, a popup will come we need to go to Font > Color and select white color.

Changing-color

 

Once we click the OK button after selecting our custom color (Here, white) the excel will hide the cells with zero values with a white background.

Zeros-hidden

Fig 10 – Output

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!