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.

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

Hide and Seek in #Excel – PivotTable Fields

Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields? I was so lost when I saw this from a workbook on my friend’s notebook. He came to … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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