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.

[Video] – Swap cell contents with a simple mouse trick

It is super easy to move cells around by drag and drop. The standard drag and drop action however replaces contents in the destination cells. What if we want to preserve destination cells and just shift them up or right? … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

[Video] How to create a dependent dropdown list in #Excel

This is the follow up post from my previous post – How to create a dependent dropdown list inΒ #Excel. As mentioned, it could be easier to illustrate the concept using a video, especially for the use of Dynamic Arrays. Here … Continue reading

Rate this:

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

How to create a dependent dropdown list in #Excel

…with and without Dynamic Arrays (which is available to Microsoft 365 users only) Got a question from a friend recently: Is it possible to show a different set of questions in a dropdown list depending on the answer input from … Continue reading

Rate this:

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

SUMIF vs. GETPIVOTDATA in terms of performance in #Excel

Did you experience a slow workbook? How slow? Let’s say it takes more than 2 minutes to just open the file. This is actually what a colleague asked me for help. When I opened the workbook, I saw LOTS of … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

The very hidden gem of AutoFilter in #Excel

Don’t get me wrong. I am not talking about the Filter button sitting on the Data Tab of ribbon, although they look almost exactly the same. I am pretty sure that most people who use Excel five days a week … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

The proper way to filter values not equal to zero in #Excel

I didn’t think of blogging about this topic before. I thought that it is so straight-forward. Nevertheless, there is another way of doing so which is even more straight-forward BUT may give you unexpected results later on. And to my … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Reapply Filter in #Excel

Did you notice that there is a “Reapply” button next to Filter on the Data Tab of ribbon? And wondering what it does? πŸ€” Indeed, the “Reapply” button is dim until you have applied filter to at least one column. … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Append tables with inconsistent column names with #Excel #PowerQuery

In the previous post Rename column names in a dynamic way with #Excel #PowerQuery, I talked about the key steps involved to rename column names in a dynamic approach; and the inspiration as well as the thinking process. As mentioned at … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Rename column names in a dynamic way with #Excel #PowerQuery

Situation: We have many different tables to be appended. It should be a simple task with Power Query. You may refer to my blogpost here for the basic of appending tables with Power Query. Nevertheless, life could be challenging in … Continue reading

Rate this:

Posted in Power Query | Tagged , | 2 Comments

Data Cleansing is never easy, even with #Excel Power Query

Don’t make me wrong. I am not complaining Power Query at all. If you have followed me for a while, you should know that I am a big fan of Power Query indeed. It is simply powerful! With Power Query, … Continue reading

Rate this:

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

Combine different ranges of data across worksheets in #Excel using Power Query

In the previous blogpost, I showed you how to use INDIRECT to prepare a summary table based on values from different ranges across worksheets. In this post, I am going to show you how to do that with Power Query. … Continue reading

Rate this:

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

#Excel INDIRECT function explained with a use case

There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. πŸ€” What INDIRECT does? It returns the … Continue reading

Rate this:

Posted in Formula | Tagged | Leave a comment

Filter: It’s easy and not easy

Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

What’s new in Excel 365?

Are you using Excel 365 but not sure what’s new? Have you heard about all the amazing new functions/features like Dynamic Arrays in Excel 365 but not sure what they are? Or you have no idea at all how Excel … Continue reading

Rate this:

Posted in General | Tagged , , , | Leave a comment

Combine files of (slightly) different layouts in a folder with Power Query in #Excel

Part 2 – What if we have inconsistent table structures across files? Note: this is a long post. πŸ˜‰ This is the continuation of previous post. Suggest you read that post first if you are new to Power Query. Moreover … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Combine files in a folder with Power Query in #Excel

Note: This is a long post… πŸ˜‰ Part 1 – What Power Query does for us in the process? If you know about Power Query, you should know that combining files in a folder is a powerful yet simple task … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Add emoji to your numbers 😁 in #Excel

Is it possible in #Excel? Of course, as you have just seen it! 😁 Indeed, it’s easier than you may have thought about. We can do it by custom format. First of all, select the cells that you want to … Continue reading

Rate this:

Posted in Excel Tips, Format | Tagged | 2 Comments

Farewell 2020… Happy New Year!!

2020 comes to an end in just a few hours . I guess it’s the first time majority of people shares the same new year wish. It was a though year. Keep in faith! Tomorrow will be a better day! … Continue reading

Rate this:

Posted in General | Leave a comment

Quick Tip – Close all #Excel workbooks at once

Have you ever had more than 10 workbooks opened at the same time? Did you spend too much time to close them all one by one? If so, you will love this simple trick to close all workbooks simultaneously! The … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Dense Ranking with Power Query – Unexpected Behavior and Workarounds

This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub. The first part of this post is a summary of what I read from Philip’s post. While the second part is … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 2 Comments