Quick Tip – Probably the quickest way to open txt file with #Excel

Are you tired of seeing the (Text Import) Wizard 🧙‍♂️ every time you open txt file with Excel?

In this super short video, you will see probably the quickest way to open a txt file with Excel.  Enjoy! 😉

Posted in Excel Tips | Leave a comment

Hide and Seek – Scroll Bars and Worksheet Tabs in #Excel

Did you encounter this?  All the worksheet tabs and scroll bars are gone in your Excel workbook!!!

Excel Tip - Hide and Seek Scroll bars and worksheet tabs

🎶🎶 Tell me… how am I supposed to work without you…🎶🎶


No worries!  They are not gone.  They are just hidden (by someone somehow 🤔).

Let’s get them back to work!  Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Lovely stickers in #Excel 365

Do you use stickers when you use communication apps like WhatsApp?  I guess that’s almost a must-have features right now for communication apps.

Would it be nice if you can use stickers in Excel too?

Excel Tips - Insert Stickers

Yes… you can, if you are using Excel Microsoft 365.


Go to View -> Insert -> Icons -> Stickers

Excel Tips - Insert Stickers1

Explore it and have fun!


Learn more here:


Posted in Excel Tips | Tagged , | Leave a comment

Hide and Seek – Formula Bar, Column and Row Headers

Have you ever experienced a shocking moment like this in #Excel?

Excel Tip - Display the formula bar and column row headers

We are so getting used to see the formula bar, Column and Row headers that we take it for granted.  They should always be there for me…. aren’t they?

What’s wrong with my Excel worksheet? 😨 Continue reading

Posted in Excel Tips | Tagged , , , | Leave a comment

Should I link to other workbooks…

…when writing #Excel formula

Note: This post is not about “security” issue 🤷‍♂️


Is the following message familiar to you?

Excel Tip - External links

I wonder… when you see this message, which button would you click?  Please let me know by leaving comments below.

From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.

Why is that?

Because most of the time they don’t have access to a “workbook” that is saved in someone else PC.  Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?

If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.

So, the question is:

Shall I link to external workbooks when writing Excel formula?

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Quick tip – Zoom in and out of the #PowerQuery editor

In both #Excel and #PowerBI

Did you know… we can ZOOM IN or zoom out everything but ribbon in the Power Query Editor in both Excel and Power BI Desktop…

Zoom InOut in the PQ Editor in Excel

… by pressing



The story behind the discovery

Continue reading

Posted in Excel Tips, Power BI Desktop, Power Query | Tagged , | Leave a comment

Insert blank columns to table in #PowerBI Desktop

In Excel, it is a common (but not necessarily a good) practice for people to add a thin blank column in a table to give a visual effect of divider.  Like the screenshot below:Power BI Trick - Insert Blank Column

To insert a blank column in Excel is super easy… probably the reason of making such practice a common one, i guess.  But when you try to do that in Power BI Desktop, you will find it super hard.  After a little of Googling, you still have no clue how to do that.

Indeed I did not and do not recommend my colleague to do so when he asked me to replicate a report from Excel to Power BI.  I said “NO” many many times.  And then one day, he sent me a Power BI page that shows blank columns as divider (see below) and kindly asked me to replicate that for him…

Power BI Trick - Insert Blank Column1

Continue reading

Posted in Power BI Desktop | Tagged , , | 4 Comments

More #Excel videos with English Voice Over…

Well… I know I know, I haven’t written new posts for a while.  I was lazy in previous weeks; enjoying summer breezes.  You know what, if I were not doing Excel on weekends, I would probably have been enjoying hiking or camping.  😎😎

Let me share some nice photos of Hong Kong.


Isn’t it nice?

Continue reading

Posted in General | Tagged | Leave a comment

Split date ranges from cells with #Excel #PowerQuery


Take a look at the challenge presented above.  Is it possible to do it in Excel?

Be honest, without Power Query, I have no idea how to do it but manual work. 😁🤦‍♂️

Believe it or not, with Power Query, this challenge can be solved in just a few minutes.

Power Query skills involved:

  • Split column by delimited into rows and columns
  • Convert date into number
  • Add conditional/custom column
  • Create a list of number based on two numbers
  • Expand list into new rows


Let’s watch it in action in my YouTube Channel: Continue reading

Posted in Power Query | Tagged , , , | Leave a comment

Replace blanks with zero in #Excel

Following the techniques used in the previous post , we can perform many Excel magics for different scenarios.  On top of my mind is to replace blank cells with zero.

Excel tip - Replace blanks with zero.png


The first task is to select all blank cells in the range.  We can do so by using the following shorcuts:

Ctrl+A => Ctrl+G => Alt+S => k => Enter

(Note: select any cell in the range first) Continue reading

Posted in Excel Tips | Tagged , , , | Leave a comment

Delete rows with empty cell(s) in #Excel

Excel Tip - Delete rows with empty cells 1

Guess what?  We are going to do it with a sequence of keyboard shortcuts only.  Believe it or not, it requires only 8 key strokes:

Ctrl+A => Ctrl+G => Alt+S => k => Enter => Ctrl+- (minus sign) => r => Enter

You may download a Sample File to follow along.

Two major steps involved:

  1. Select blank cells in the range
  2. Delete “Entire row” of those selected blank cells

The keyboard shortcuts for different actions are listed below:  Continue reading

Posted in Excel Tips, Shortcut | Tagged , | Leave a comment

Transform and Combine all worksheets in an #Excel workbook with #PowerQuery

Power Query is magically powerful, yet it is not well known to majority of Excel users.  I think most Excel users are not even aware of its existence…  So, let’s talk about how Power Query could solve a real-life problem commonly found at workplace. 😉

One day, I got an IM message from a colleague who attended a few of my in-house Excel Training courses… (I didn’t hold any Power Query session yet; so she has no idea of Power Query at all.)  She asked:

If there is a quick way to combine (append) 100+ worksheets in a workbook. 

I replied:

“Of course… only if you know how!”  😁 I was bad, I know.

I then asked her to share her workbook with all sensitive information removed.

Here’s the content of the worksheets (all data are fake… of course):

Excel Tips - Transform and Combine all worksheets

What she wanted to extract from the worksheets is the range highlighted above, with the metadata “Employee Name” and “Employee ID” being put together.

Here’s the layout of the expected outcome:

Excel Tips - Transform and Combine all worksheets2

No empty columns; no empty rows… just a simple table!

The real challenge

You may be thinking… that’s easy… it can be done in just a minute.  BUT remember this, there are more than 100 worksheets in the workbook and we need to do that to each worksheet.

Excel Tips - Transform and Combine all worksheets3

Then we have to combine all transformed worksheets into a single table:

Excel Tips - Transform and Combine all worksheets4

AND needless to say, we may have more worksheets (employees) in the workbook that we want to apply the same transformation and consolidation in the future.

How much time we need to do it manually?

I guess it could take a full day, or even more…

No worry!  Power Query comes to rescue!

Continue reading

Posted in Power Query | Tagged , , | Leave a comment

My first video with English Voice Over

Power Query – Full Outer Join Revisit Part 2

If you have been following my blog for a while, you should probably know that I’ve started my YouTube channel about one year ago.  Up to this moment, I’ve uploaded 54 videos.  Almost one video per week.  Not bad as a part-time YouTuber, I guess.  😅

And you know, most of the videos (in the Excel Magic in One Minute) are silence, without any voice over.  For the rest, I recorded them in Cantonese, which is my mother tongue.

I know Cantonese is a very niche segment for Excel learning.  And I know majority of YouTube viewers do not understand Cantonese.  Therefore I put English subtitles on all my videos with Cantonese voice over.  I planned to continue in this way until one day I read the following comment from bebuger bebuger:


Thank you very much for your kind words, bebuger!  It’s very encouraging! 😉

With your encouragement, here comes my first video in English:

I hope you like it.


Here’s the Cantonese version:


It turned out that recording English Voice Over is much more efficient.  For a 10-minute video, it took me 5+ hours in preparing the English subtitles. I had to write the script, put it onto the video, and sync it with the video.  Indeed it’s a quite tedious and time consuming.

This time, I recorded the video in Cantonese first.  After editing the video, I recorded English voice over on it.  Although it took me many times for the recording… although my fluency and intonation was not good, I finished it in just more than one hour… Cool.  😎  I should have done this earlier.

If you like my videos, please give a thumb up 👍, share it and SUBSCRIBE.  😉

Thanks again, bebuger.  I hope I will have more subscribers as a result.

Posted in General | Tagged , | Leave a comment

Power Query Challenge – Joining two tables fully (Part 2)

Append Queries + Group By = Another alternative for a better Full Join

In the previous post, we explored an alternative way to perform FULL OUTER JOIN with Left Outer Join + Right Anti Join, followed by Append Queries in order to get a result with better layout.  Well, as it’s Excel, that means there are different ways to achieve the same.  In this post, let’s see how we can do the same using Append Queries + Group By.

Again a picture tells thousand words.  The following diagram illustrates the concept:

Power Query Full Outer JOIN Revisit

In the end of this post, I will post a video (work in progress 😅) riding on the example in previous post to demonstrate it.  From which you will see the two different approaches yield the exactly the same result.

For illustration purpose, two simple tables are used for this post.  Let’s see how to do it step by step in Power Query!

You may download a Sample File to follow along.

Continue reading

Posted in Power Query | Tagged , | 1 Comment

Power Query Challenge – Joining two tables fully

Left Outer + Right Anti = A better Full Join

Excel Tip - Full Outer Join revisit

A sample file is available below.

If you prefer reading to watching, please continue to read this post.

The situation:

I have two tables.  One is Sales Table; One is Traffic Table.  What we need to do is to combine them into a single fact table.

Excel Tip - Full Outer Join revisit1

With Power Query, it is an easy job.  We can merge the two tables with Left Outer Join!

BUT… If we do that with Left Outer Join, we will miss some data points.  The following screenshot depicts the situation:

Excel Tip - Full Outer Join revisit2

Those traffic records with “?” do not have a matching record in the Sales table.  As a result, those traffic records are missed in the output, and most of the time without any notice/warning… 😑  Continue reading

Posted in Power Query | Tagged , | 2 Comments

Idea: Ctrl+M to launch #PowerQuery Editor

To all Power Query Users:  I need your vote!

Idea - CTRL M to launch Power Query Editor

Well… we know that in Excel

  • the language behind Power Query is called M;
  • it takes at least three clicks to launch Power Query Editor;
  • the shortcut CTRL+M is currently unassigned…
Then why not using CTRL+M to launch Power Query Editor?
If you like this idea, please vote and make it happen:


Posted in General, Shortcut | Leave a comment

Determine leap year with #Excel

Leap Year

Today is a special day.  Today is February 29th, which happens only once every four years.  And today is Saturday, when I usually post a new blog.  So let’s talk about how to determine if a year is leap year using Excel.

First of all, what is leap year?

We may find a detailed explanation in WikipediaBelow is part of the article describing how to determine a leap year without needing an Excel formula.

…in the Gregorian calendar, each leap year has 366 days instead of 365, by extending February to 29 days rather than the common 28. These extra days occur in each year which is an integer multiple of 4 (except for years evenly divisible by 100, which are not leap years unless evenly divisible by 400)


But you know, this is an Excel blog and I am an Excel nerd.  That’s why I am going to use Excel to solve this problem.  A simple formula indeed.

'where A2 is the year input; works for year 1901 onward
It's an known issue that Excel mistreats the year 1900 as leap year. 😐

How the formula works?

  1. The most inner portion DATE(A2,2,1) returns the first date of February of the year;
  2. Wrapping it with EOMONTH, Excel returns the last date of February of the year;
  3. The outermost DAY() function returns the day portion of the date;
  4. As a result, if it is 29, it’s a leap year. 

As simple as that.  🙂

Indeed, we may use a much shorter formula to achieve the same.


When we hard-code the day portion as 29, and when there is no 29th in that February, Excel is smart enough to return March 1st instead.  Therefore the outside DAY() function would return 1, which is not equal to 29.

Make sense!?

Want some challenge?

Well, you may think it is too easy to determine if a year is leap year of not.  Really no need to use Excel to do that.  How about if I twist the question a bit…

When is the next February 29th which is a Saturday?

Think a bit before you continue to read this…

Tip: We can do it in a single formula by using Dynamic Array 

Continue reading

Posted in Formula | Tagged , , , , , , , | 2 Comments

So many different ways. That’s #Excel!

Last week, I posted a video and blogpost in response to a challenge from Mr. Excel .  And this week, Mr. Excel has collected viewers’ solutions and post a video to summarize different techniques used for solving the same question.  Very informative!  Check it out:


Btw, you have no idea how happy I was when I heard Mr. Excel mentioned my name in his videos many times.  Feeling so honored. 😊  Thank you Bill!

Posted in Power Query | Leave a comment

Power Query Challenge from Mr Excel – Reshaping data

I really like Power Query (in both Excel and Power BI).  Honestly I do not know much about the M behind the scene (still learning it piece by piece).  Nevertheless, I’ve got the POWER simply through the User Interface of Power Query Editor.  Seriously,  everyone can become a data wizard by using Power Query!  Let’s watch the following two videos to feel the POWER of Power Query.

Here’s is a challenge (with solution) from Mr Excel on how to reshape data.

You may download a sample workbook from Mr.Excel YouTube channel to follow along.


And here’s my approach to solve the challenge:


If you prefer reading to watching, please continue to read this post.

Note: All screenshots used in this post are captured using Excel for Office 365.  If you are using other versions of Excel, you may find the locations and look&feel of buttons a bit different. The concept is the same though.


Continue reading

Posted in Power Query | 1 Comment

Compare two #Excel workbooks for changes

Excel Tips - Inquiry0

In about 5 years ago when I was still using Excel 2010, I wrote a post Compare two documents for changes – Word and maybe ExcelLukas left a comment:

In the Inquire Add-In you have the option to compare Workbooks the same way that you describe for Word…

At that time, I didn’t have the Inquire Add-In… and neither had I ran in a situation that I really needed it.

Not until recently, I encountered a situation at work that I really needed to compare two workbooks for changes.

Here’s the situation:

This is a file with translation that I need local team to review…  As the translation agency would need to know what’s commented, they asked us to write our comments on Column C…


I did expect the reviewer would return the file with comments on column C… Nevertheless, this is what I’d got:


OMG…  Instead of writing on column C, it’s (over)written on the original translation on column B.

(I guess not following instruction when using Excel is a common practice at workplaces! Isn’t it? 😑)

I was so happy that I knew about the Compare Files feature under Inquire Add-in.

I am going to show you what it is now! 🙂

You may download the following two files to follow along:

Continue reading

Posted in Excel Tips | Tagged , | 2 Comments