When you work in Excel, there are situations when you want to either show zero in some cases or hide zero in other.
This tutorial will teach you how you can achieve this using two different methods.
Format cells to display blank instead of zero
In the first example, we are going to use the SUM function to sum a few numbers.
The result in some cells (B7, C7) is zero. There is a way to display these numbers as blanks by using number formatting.
In order to format the cells, select cells from A7 to E7 and use the right mouse click. From the contextual menu select Format Cells.
A new window will appear. Select Number tab and click Custom Category.
Inside the Type filed, you have General. It’s the default formatting for numbers and text.
You can change it by using your own formatting. Here’s how it works.
This format is divided into a maximum of 4 parts:
- Positive numbers
- Negative numbers
We are going to use the following format:
It will keep positive numbers as positive, negative numbers as negative, but avoid zeros and text. In practice, this means that zeros will be displayed as blanks.
You can easily modify this format to display dashes (-) instead of zeros.
This will give the following result.
The other way to display blanks or dashes, instead of zeros, is by using the IF function.
So far we used this formula for cell A7.
Let’s modify this cell, so it’s going to display blank if the result is 0.
Modify the remaining cells. It will give you the following result.
Of course, slight modification will result in dashes instead of blanks.
Hide all zeros inside a worksheet
So far, we replaced zeros in the selected range. If you want to hide zeros for the entire worksheet you can do it inside Excel options.
Go to File >> Options. Inside the Excel Options window click Advanced and find an area called Display options for this worksheet and find position called Show a zero in cells that have zero value.
Now, you can see that not only the cells that are in row 7 are blank, but also these inside the table. These cells are treated as zeros and displayed as blanks.