When we try to reference blank cell by using “=” , you may find the blank value turns into a zero. This is because when you use “=”, you are basically telling Excel to perform a mathematical operation. That’s why Excel will give you a mathematical value. So, what we are going to do is to tell Excel that we don’t want a mathematical value.
In this article, I will show you how to display blank when referencing blank cell in Excel. I will provide you with 4 ways to achieve the result. You can choose between the 4 ways based on your need.
If you have the following questions, I suggest you reading this until the end.
- How to display blank when referencing blank cell in Excel?
- How to display blank instead of zero?
- How to return zero when a referenced cell is blank?
You might also be interested in How to get value from every nth row?
Example
In the above example, the below table is referencing the above table.
The formula of cell A6 is
=A1
As you can see, there are two cells showing “0”(cell B7 and cell D8). They are referencing cell B2 and cell D3 respectively.
What we would like to do
To display blank when referencing blank cells
You might also be interested in How to prevent duplicate entries in Excel?
Display Blank when Referencing Blank Cell by Formula
There are two ways to do so.
First formula (formula of cell A6)
=A1&""
For the remaining cells, just copy this formula over.
When you add “” to cell A1, Excel will perceive the outcome as a text. So, it will store the cell as a text. Some people may worry that the text format may affect the mathematical operation of the cell. You don’t need to worry about this because Excel will convert it to a numeric data when you use it in an mathematical operation.
In case if anyone is confused about why Excel perceive “” as a textual value. I will explain it briefly. When we try to put a text in the cell, we have to use brackets to tell Excel that it is a text. When you put nothing in the brackets, Excel still perceive it as a text and will display nothing in the cell.
This one is handy when your work is informal and does not require a lot of effort.
Second formula (formula of cell A6)
=IF(A1<>0,A1,"")
For the remaining cells, just copy this formula over.
This is a basic IF statement. It is basically saying
“If cell A1 is not equal to 0, display the value of cell A1. If it is equal to zero, display nothing”
You might also be interested in How to Concatenate Date and Text in Excel.
Display Blank when Referencing Blank Cell by changing options
With this solution, you don’t need to change your referencing formula. This is especially useful when you have a long and complexed formula and you don’t want to further complicate it.
Step 1 Press “File”
Step 2 Select “Options” on the left panel
Step 3 Select “Advanced”
Step 4 Scroll down until you find the below section and uncheck the “Show a zero in cells that have zero value” box
Step 5 Press “OK”
The only drawback of this method is that it is not as flexible as using the formula. Since the options will affect the whole worksheet, you won’t be able to change the display of certain cells within the sheet.
So I will only suggest people using this method if they are happy with displaying blank whenever they have a zero value.
You might also be interested in How To Remove Digits After Decimal In Excel?.
Display Blank when Referencing Blank Cell by conditional formatting
If you don’t want to change your formula and still want to be flexible, you may want to try the conditional formatting.
Step 1 Select the cell range where you would like to apply the conditional formatting
Here I selected cell A6 to cell F9.
Step 2 Select “Home” > “Conditional Formatting” > “Highlight Cells Rules” > “Equal To…”
Step 3 Enter “0” in the left box and select “Custom Format…” from the drop down menu
Step 4 Select white box for the colour and press “OK”
Step 5 Press “OK”
And you are done. The cell that contains 0 should appear as if it doesn’t contain anything now.
Actually all we have done is simply changing the font colour of the cell that contains zero. Here I changed the background colour of the cell. You can see that the zero is still there.
I personally won’t recommend the conditional formatting way.
- First, it increases the calculation time of your worksheet which may end up making your sheet slower.
- Second, you may accidentally mess up the conditional formatting when you insert or delete rows or columns. That way you will need to edit the conditional formatting a lot.
- Third, if the background colour of your cells is not white. You will have to apply different conditional formatting to cells with different background colour.
Display Blank when Referencing Blank Cell by Number Formatting
If you have changed the background colour of your cells a lot, the conditional formatting way may appear to be a bit time-consuming. You may want to try the following way out.
Step 1 Select the cell range where you would like to change the number format
Here I selected cell A6 to cell F9.
Step 2 Select “Home” > the small arrow next to the “Number” section
Step 3 Select “Custom” > input “General;-General;”>Press “OK”
And you are done!
How does it work
To understand how does the above method work, we will need to dig into the Excel custom number format structure.
The structure consists of 4 parts.
- Format for positive numbers
- Format for negative numbers
- Format for zeros
- Format for text
For the code we used above, there are only 2 visible parts. Although part 3 and part 4 cannot be seen, it doesn’t mean it doesn’t work. It simply means it remain default format.
For part 1, we entered “General” > display its integer absolute value if it is a positive number.
For part 2, we entered “-General” > display its integer absolute value preceded by a minus sign if it is a negative number.
For part 3, we entered nothing >display nothing if it is zero
For part 4, we entered nothing >display text if it is a text
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
