Author Archives: MF

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 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.

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! 😉

Rate this:

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!!! 🎶🎶 Tell me… how am I supposed to work without you…🎶🎶   No worries!  They are not gone.  They are just hidden (by … Continue reading

Rate this:

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? Yes… you can, if you … Continue reading

Rate this:

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? 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? … Continue reading

Rate this:

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? I wonder… when you see this message, which button would you click?  Please let me know by leaving comments … Continue reading

Rate this:

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… … by pressing CTRL+SHIFT+= CTRL+SHIFT+- respectively. The story behind the … Continue reading

Rate this:

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: To insert a blank column in … Continue reading

Rate this:

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 … Continue reading

Rate this:

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, … Continue reading

Rate this:

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.   The first task is to select all blank cells in … Continue reading

Rate this:

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

Delete rows with empty cell(s) in #Excel

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 … Continue reading

Rate this:

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 … Continue reading

Rate this:

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 … Continue reading

Rate this:

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 … Continue reading

Rate this:

Posted in Power Query | Tagged , | 1 Comment

Power Query Challenge – Joining two tables fully

Left Outer + Right Anti = A better Full Join 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 … Continue reading

Rate this:

Posted in Power Query | Tagged , | 2 Comments

Idea: Ctrl+M to launch #PowerQuery Editor

To all Power Query Users:  I need your vote! 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 … Continue reading

Rate this:

Posted in General, Shortcut | Leave a comment

Determine leap year with #Excel

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 … Continue reading

Rate this:

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.  … Continue reading

Rate this:

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 … Continue reading

Rate this:

Posted in Power Query | 1 Comment

Compare two #Excel workbooks for changes

In about 5 years ago when I was still using Excel 2010, I wrote a post Compare two documents for changes – Word and maybe Excel.  Lukas left a comment: In the Inquire Add-In you have the option to compare … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 2 Comments