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.

Replace values in filtered ranges using Copy and Paste trick in #Excel

Situation: In a filtered range of data, we made few changes on the side. Then we want to replace the original values with the updated values. What action appear on top of your mind? Copy and Paste of course. But… … Continue reading

Rate this:

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

Have you encountered any weird formula in #Excel?

Happy Halloween! ๐Ÿ‘ป Let’s do something fun. You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. ๐Ÿ˜ Wait… what do I mean … Continue reading

Rate this:

Posted in Formula | Tagged | 1 Comment

Input data on one worksheet… Get summary on another worksheet in #Excel

It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one … Continue reading

Rate this:

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

SUM the last X non-blank values in a column in #Excel

Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He … Continue reading

Rate this:

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

Understanding Find and Replace in #Excel

Find and Replace is a very handy tool in Excel. It helps you to find a value quickly. You may then replace the value found with another value, either one by one or replace all at one batch. To use … Continue reading

Rate this:

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

#Excel #PowerQuery Challenge – Add value to CSV data in cells

As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course. This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by … Continue reading

Rate this:

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

Learn how to build effective #Excel Dashboard from Mynda Treacy

Did you know… you can create such a beautiful dashboard using Excel? Did you also know… this visually appealing dashboard was built in just more than 30 minutes? No kidding! Check it out HERE and you will see. Want to … Continue reading

Rate this:

Posted in General | Tagged , | Leave a comment

Combine all visible worksheets from multiple #Excel files in a folder

It is a common task for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. ๐Ÿ˜ Wait? What about if you want to combine … Continue reading

Rate this:

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

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 , , | Leave a comment

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