Learn something new from something that I think I knew well…

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.

Excel Tip - Return nothing instead of 0

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.  温故知新。

Advertisements
This entry was posted in Formula and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s