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

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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

This site uses Akismet to reduce spam. Learn how your comment data is processed.