Return Blank Cells Instead of Zeroes in Excel Formulas: Easy!

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

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.

1. Hide zero values with conditional formatting rules.
2. Blind out zeros with a custom number format.
3. Hide zero values within the worksheet settings.

For details about all three methods of just hiding zeroes, please refer to this article.

One small advice on my own account: My Excel add-in “Professor Excel Tools” has a built-in Layout Manager. With this, you can apply this formatting easily to a complete Excel workbook.

Give it a try? Click here and the download starts right away.

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.

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 blank or empty return values

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))

Option 4: Use Professor Excel Tools to insert the IF functions very quickly

To save some time, we have included a feature for showing blank cells (instead of zeros) in our Excel add-in Professor Excel Tools.

It’s very simple:

1. Select the cells that are supposed to return blanks (instead of zeros).
2. Click on the arrow under the “Return Blanks” button on the Professor Excel ribbon and then on either
   1. Return blanks for zeros and blanks or
   2. Return zeros for zeros and blanks for blanks.

Professor Excel then inserts the IF function as shown in options 2 and 3 above.

Option 5: One elegant solution for not returning zero values…

… was written in the comments. Thanks, Dom! Great to get such awesome feedback from you!

It’s actually quite straight forward: Add two quotation marks in front or after the function and concatenate it with the “&”-character. So, in this case the VLOOKUP function from above would look like this:

=""&VLOOKUP(A3,C:D,2,FALSE)

But: There is a downside. This method forces the cell to a text / string value. This might cause problems in further calculations if a zero is returned. Let me give you an example:

Careful: Return value is interpretated as text.Your VLOOKUP function above is concatenated to an empty string “”. If your return value is 0, the cell will be shown as an empty string. Further calculations might be difficult: For example, if you want to add a number to the return value. Referring directly to the cell, for example =F6+G6 leads to the #VALUE error message as shown above. However, using the SUM function =SUM(F6,G6) still works.

Bottom line: This is an elegant approach, but please test it well if it suits to your Excel model.

Image by Pexels from Pixabay