Today is a public holiday in Hong Kong.  A perfect break in the middle of a busy week.  What did I do on a public holiday?  Excel, Excel and Excel. 🙂

I watched a few videos from my favourite YouTube channels, and learned something new (to me).  Probably you think I learned some cool new features of Excel 365.  Yes I did.  Although the new features are really impressive, they are not as much as surprising than a simple trick I learned from a video in Excelisfun.

It is s a simple trick to return blank instead of “0” when a formula references to an empty cell.

As you may know, when a formula references to an empty cell, it returns “0” instead of blank.  To avoid that, simply concatenate the formula with “” (empty string).  As simple as that (as shown in B4 in the above screenshot).

Note: The result returned is Text.  If you are expecting the results returned to be Number (for further calculation), then this approach may not be ideal.  Try the following custom format instead.

#,##0;-#,##0;;@
'This custom format returns 0 to nothing.

Here’s the video I watched:

This is a great video about VLOOKUP.  I highly recommend you watch the whole video if you want to learn more about VLOOKUP.  The trick I mentioned in this post starts at 56:28 though.

BTW, I have many posts about VLOOKUP too. 🙂

Don’t refuse to re-examine something you “think” you knew really well.  We could learn something new from something knew.  温故知新。