How to Hide the Zero (0) Values in Excel

In this article, we will learn How to Hide the Display of Zero Values in Excel.

Scenario:

Problem here is working with excel data, sometimes we need to treat 0 as a blank cell. For example getting to know the average time spent on work each day. Now you think why it is a problem as the average function gives different results for data with 0 values and same data with blank cells. Another example can be printing out a data page with blank cells, no zero values. This happens because zero in excel is a number not a blank cell. So to avoid zeroes in your data use one of mentioned methods depending upon your usage. Not every method can be used for each purpose as in some methods zero is made invisible. So make your choice the right way.

First way : Custom format

Select the whole table and Go to Format cells Dialog box by going to Home tab, click Format > Format Cells or just using Ctrl + 1 keyboard shortcut. Then Go to Custom type 0;-0;;@ and click Ok.

You can get back zeroes by switching it back to General.

As you can see from the snapshot, This will make the data have more meaning than with zero values. This method is easy and convenient and is the common practice.

Second way : using Advanced Options

Another way using the Excel Advanced options. To make changes to the whole sheet and needed that change for later purpose. So go to File > Options > Advanced. And

Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.

To display zero (0) values back in cells, check the Show a zero in cells that have zero value check box. As you can see from the snapshot, This will make the data have more meaning than with zero values.

Third way : using IF function

It's the space consuming way but very effective for calculation with functions like AVERAGE function which considers blank cells and zero (0) values differently.

Use the Formula for the first cell, =IF(cell_ref=0, "", cell_ref) And then copy the formula to all other cells to get the data with blank cells. Now you can use this data wherever you need either for calculation or printing.

Fourth way : Using Conditional formatting

This is the way to make zero (0) invisible with the background data, so zero values in the table don't get printed. Generally the excel cell background is White. So you can either change the background color to the same color of font or vice-versa. Here we select the data and Go to Conditional Formatting > Highlight Cells Rules > Equal To.

This will open up the Equal to value box.

In the box on the left, type value as 0 and In the box on the right, select Custom Format. It will open up the Format Cells box.

Click the Font tab as shown in above snapshot .In the Color box, select white, and then click OK. Or switch it to whatever background color you are working on.

Here are all the observational notes using the formula in Excel
Notes :

1. Some of the methods just make 0 invisible with other data. Infact the value of the cell constants is still 0. So don't opt for these methods for calculation.
2. Choose any method to hide the display of zero in date.

Hope this article about How to Hide the Display of Zero Values in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

Ignore zero in the Average of numbers : calculate the average of numbers in the array ignoring zeros using AVERAGEIF function in Excel.

How to Convert date to text in Excel : In this article we learned how to convert text into date, but how do you convert an excel date into text. To convert an excel date into text we have a few techniques.

How To Highlight Cells Above and Below Average Value : highlight values which are above or below the average value using the conditional formatting in Excel.

Calculate Weighted Average : find the average of values having different weight using SUMPRODUCT function in Excel.

Average Difference between lists : calculate the difference in average of two different lists. Learn more about how to calculate average using basic mathematical average formula.

Average numbers if not blank in Excel : extract average of values if cell is not blank in excel.

AVERAGE of top 3 scores in a list in excel : Find the average of numbers with criteria as highest 3 numbers from the list in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.