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.

Do Not input “-” for zero. Use proper formatting in #Excel

Do you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it. E.g. Using Accounting format with no … Continue reading

Rate this:

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

Convert numbers of different digits into text of fixed digits using #PowerQuery

This is an extension of the previous post. Here’s the task: If the data is coming from a CSV file, the solution is super easy. You may download the sample CSV file to follow along.

Rate this:

Posted in Power Query | Tagged , | 2 Comments

Convert numbers of different digits into text of fixed digits in #Excel

The situation We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work … Continue reading

Rate this:

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

How to replace value with a value from another column in Power Query

It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

The Power BI Journey

Note: This is not a “how-to” post. In this article, I share my thoughts on how people start their Power BI journey, based on my observation and imagination. 😁 When you say you want to learn Power BI, what do … Continue reading

Rate this:

Posted in General, Power BI Desktop | Tagged | Leave a comment

7 Copy and Paste Tricks I wish I knew sooner in #Excel

Regardless of what we do with Excel, Copy and Paste is something we do on daily basis… or even hourly basis. 😁 You may be thinking, every one knows Copy and Paste. Yes, probably. But is every one using Copy … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Protect your cells in #Excel

Think about this… you have spent hours of work building your wonderful Excel template. Needless to say there are lots of formulas that took you hours of thorough thoughts and considerations, on top of the beautiful formats and layouts. You … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

[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 | 2 Comments

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