Hide Zero Values in the Selected Cells Using Format Cells

Follow the below steps to hide Zero Values in the selected cells using Format cells:

Step 1: Select Cells and Select Format Cells

Select the cells or set of cells from where you want to hide zeroes. Now Right-click and select “Format cells”.

Step 2: Click on Custom and Enter Custom Type

In the “Format Cells” dialog box, Under Number, click on the “Custom” option and then enter the custom type as “0;-0;;@” and click OK.

Step 3: Click Ok and Hide all Zero Values

After clicking Ok you can hide all the zero values within the selected range.

The Custom formatting string “0;-0;;@” is composed of four distinct elements:

The initial “0” is for positive numbers, retaining them in their original state. The subsequent “-0” addresses negative numbers, presenting them as is. And the third element, “0” is absent intentionally to effectively hide zero values. The last element, denoted by “@” symbol, ensures the unaltered display pf text : ;;;.

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.