Return Blank Cells Instead of Zeroes in Excel Formulas
If the return cell in an Excel formula is empty, Excel by default returns 0 instead. For example cell A1 is blank and linked to by another cell. But what if you want to show the exact return value – for empty cells as well as 0 as return values? This article introduces three different options for dealing with empty return values.
Option 1: Don’t display zero values
Hide zero numbers in Excel within the worksheet settings
Probably the easiest option is to just not display 0 values. You could differentiate if you want to hide all zeroes from the entire worksheet or just from selected cells.
There are three methods of hiding zero values.
- Hide zero values with conditional formatting rules.
- Blind out zeros with a custom number format.
- Hide zero values within the worksheet settings.
For details about all three methods of just hiding zeroes, please refer to this article.
Option 2: Change zeroes to blank cells
Unlike the first option, the second option changes the output value. No matter if the return value is 0 (zero) or originally a blank cell, the output of the formula is an empty cell. You can achieve this using the IF formula.
Structure of the IF formula for changing zeroes to empty values.
Say, your lookup formula looks like this: =VLOOKUP(A3,C:D,2,FALSE) (hereafter referred to by “original formula”). You want to prevent getting a zero even if the return value―found by the VLOOKUP formula in column D―is an empty value. This can be achieved using the IF formula.
The structure of such IF formula is shown in the image above (if you need assistance with the IF formula, please refer to this article). The original formula is wrapped within the IF formula. The first argument compares if the original formula returns 0. If yes―and that’s the task of the second argument―the formula returns nothing through the double quotation marks. If the orgininal formula within the first argument doesn’t return zero, the last argument returns the real value. This is achieved by the original formula again.
The complete formula looks like this.
=IF(VLOOKUP(A3,C:D,2,FALSE)=0,"",VLOOKUP(A3,C:D,2,FALSE))
Option 3: Show zeroes but don’t show empty return values
Structure of the IF formula for returning an empty cell if the return value is also an empty cell.
The previous option two didn’t differentiate between 0 and empty cells in the return cell. If you only want to show empty cells if the return cell found by your lookup formula is empty (and not if the return value really is 0) then you have to slightly alter the formula from option 2 before.
Like before, the IF formula is wrapped around the original formula. But instead of testing if the return value is 0, it tests within the first argument if the return value is blank. This is done by the double quotation marks. The rest of the formula is the as before: With the second argument you define that—if the value from the original formula is blank—the return value is empty too. If not, the last argument defines that you return the desired non-blank value.
The formula in your example from option 2 looks like this.
=IF(VLOOKUP(A3,C:D,2,FALSE)="","",VLOOKUP(A3,C:D,2,FALSE))
Comments 9
Stuart
Hi Henri can you help me with what is I’m sure a simple problem.
I have text in column A which is one of 4 options. In a related row I have times. I want a function to find the longest time in each text option. An example would be:
A 01:30
B O2:20
A. 02:05
C. 01:31
Etc
Can you help?
Stuart
Dom
Options 2 and 3 are seriously ugly solutions. By far the most elegant approach is to concatenate a null string to the lookup result, thus forcing the result to a string:
=””&VLOOKUP(A3,C:D,2,FALSE)
Ali
Option 3: Show zeroes but don’t show empty return values
sir this formula not working,
show zeros but a same time show the empty return values. please fix this problem
Topher
How is Option 3 any different from just using the original formula?
Henrik Schiffner
If the value in your original formula is blank, the original formula would (without the if-formula according to number 3) return 0. Using option 3 changes it to blank again.
You can easily try it by just using a cell reference, for example writing =B1 in cell A1. If you leave B1 blank, A1 would show 0. Using the option 3 would show a blank cell A1.
jay klein
greetings
I have a sumif formula applied to a column that has blanks in it, and in some cases may have ALL blanks. I need to know that when I see a zero it is a ZERO, not a blank being displayed as a zero. I have tried everything.
mll
@Dom: your trick is great, as long as we only need to prevent zeroes to display.
but if I need to do for example
=VLOOKUP(A3,C:D,2,FALSE)<1
cases where VLOOKUP(A3,C:D,2,FALSE) = “” will display TRUE instead of the desired blank value
If we do
=””&VLOOKUP(A3,C:D,2,FALSE)<1, it will always display FALSE whatever the case.
So finally we have no choice but to revert to the – ugly, I concur – suggestion of the article, which is
=IF(VLOOKUP(A3,C:D,2,FALSE)=””;””;VLOOKUP(A3,C:D,2,FALSE)<1)
Noel
Thanks for this. Option 3 works great for my database.
Erik
Wouldn´t ORIGINAL FORMULA&”” work as well?
That displays the cell as empty if the value is 0