Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most suited to their situation.

Consider this worksheet:



In columns A:B, we have a list of employees and the days that they work. In D1:J7 there's a table organizing columns A:B into a grid. Then in D10:J16 is the same table, but without the zeros showing. For now that second table just has values, no formulas. But it's much easier to read, and you can spot several patterns much easier, such as Stacy only works on Tuesdays, Tuesdays and Wednesdays seem to be understaffed, and Isabella always seems to work the same days as Phineas.

So how do we get from table 1 to table 2?

Option 1: Probably the easiest is to go to File > Options > Advanced > scroll down to "Display options for this worksheet:" > and uncheck the box that says "Show a zero in cells that have zero value". About the only real drawback to this option is that it's a sheetwide setting. If you have other ranges on the same sheet with zero values you want to see, this will hide them too.

Option 2: You can apply a custom format. With the sheet above, select the range E2:J7, right click the range, select "Format cells" > choose the Number tab > select the Custom category > and enter

General;"-"General;;@

in the Type: box. Using this method, you can restrict the format to just the ranges you want. The only downside to this is if you already have a specific format applied to those cells. You'd have to adjust the custom format to handle the existing format. If that turns out to be too difficult, an extreme variation of using a custom format would be to use Conditional Formatting. Again, select E2:J7, from the Home tab click Conditional Formatting > New Rule > Use a formula > Enter this formula

=E2=0

then click Format... > Number tab > Custom > and enter in the Type: box :

;;;

This would only apply the ;;; format to the cells with zero values, and the format on the other cells in the range would stay unchanged. This is not especially recommended, other methods are better.

Option 3: There are times when you don't want to change a sheetwide setting, or use a custom format. In that case, you need to change the formula itself. You can change the formula above to:

=IF(COUNTIFS($A:$A,$D2,$B:$B,E$1)=0,"",COUNTIFS($A:$A,$D2,$B:$B,E$1))

Just wrap the IF around it, checking for a zero result. If zero, display "". If not, repeat your original formula. This should work for all versions of Excel. (I'm aware that COUNTIFS originated with Excel 2019, but the principle remains for other functions.) Two minor downsides. First, the formula is twice as long, since you have to enter the original formula twice. Second, if the number is 0, the result is "", which is not numeric. This could throw off other formulas that refer to that cell, such as COUNT.

Option 4: This is a variation of option 3 which doesn't require using your original formula twice. If you have a version of Excel that has IFERROR (Excel 2010 or newer), you can change the formula like this:

=IFERROR(1/(1/COUNTIFS($A:$A,$D2,$B:$B,E$1)),"")

This looks confusing at first. Depending on who you talk to, this is either a clever trick, or an ugly hack. How it works is by finding the reciprocal of the result of your formula. For all non-zero values, you get another number. For zero, you get a #DIV/0! error. Then take the reciprocal again. For non-zero values, you'll get your original value. If you already got the #DIV/0! error, it will remain an error. Then the IFERROR function detects it and displays "" instead of the error code.

Downsides to this are that it's a bit confusing if you haven't seen it before. It also has the same issue as option 3, in that the result is a text value, not a number. Finally, I've heard some reports that it doesn't always work, depending on the original formula and rounding issues. I've never seen a problem, although it's best to be aware.

This raises another point though. These methods work on values that are exactly zero. If you have a very, very tiny number, you might need to use ROUND to hide it, again depending on your situation.


LOOKUP FUNCTIONS:

Another variation of this question is how to handle lookup functions when the result is empty. Consider:



Columns A:B have some names and codes. D2:D5 have some names, and a simple VLOOKUP in E2:E5. All of the results look ok, except for E3. The value in the table is blank, not 0. This is a common issue with all the lookup functions, even if you try something as basic as

=B4

you'll get a 0. So how do you get a blank like the table says? The easiest way I know is to add &"" to the end of the formula, see cell E9. Piece of cake! Well, there is a downside, it makes everything that it returns into a text value. If everything starts as a text value, there's no problem. But if some of the returned values are numbers, then they become text representations of numbers. This could be a problem, see the SUM formula in F12. It's trying to sum up E11:E12 and coming up with 0, because those cells now contain text and not numbers, even though they still look like numbers. If that's an issue, you may need to use the E16 formula (which is a variation of Option 3 above).


So those are all the ways I know to display zeros as blanks. If someone knows another way, please leave it in the comments.