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.

Got bitten by an undefined data column in #PowerQuery

The importance of explicit data type in Power Query What could go wrong when we have a column of “ANY” data type in Power Query? Well, it depends. Depends on what? Sometimes luck! 😅 This query has been working well … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Add Index Column in #Excel using #PowerQuery

In Power Query, it is a super easy task to add a column of sequential numbers. We can do it by adding an Index Column. There are three options for adding Index Column: This can be demonstrated in a 1-minute … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Add Column From Examples with #Excel Power Query

Use it with caution although it’s a powerful feature! In the previous post, we talked about how to add conditional columns in Power Query in which we identified the store type of a store by the prefix of the store … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Add conditional columns with #Excel Power Query

Situation: We are given a simple table on the left. We need to add three additional columns based on the prefix of the StoreID, and the sales values. The table on the right is the expected outcome. And here’s the … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Simple Basket Analysis – Formula approach with #Excel 365

The challenge – How many transactions with just Brand A and B together? In my previous post / video, I solved this problem with Power Query. If you are using #Excel 365, did you know you can solve this problem … Continue reading

Rate this:

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

Power Query Challenge – Simple basket analysis

The challenge – How many transactions with just Brand A and B together? This is a common request in retail business to analyze massive amounts of transactional data. A transaction table usually contains a lot of records. As you see … Continue reading

Rate this:

Posted in Power Query | Tagged | 2 Comments

Distinct count using functions in #Excel 365 is so easy

Have your ever wanted to count how many distinct items in a range? Though a common task, there was not build-in functional for it. Perhaps we can make a distinct list using advanced filter first then we count the outcome. … Continue reading

Rate this:

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

The modern way to shortcut in #Excel 365

We all know that shortcut keys boost productivity in Excel as we can perform certain actions quickly with just a few keystrokes… if we can memorize the shortcut key combinations. Be frank, how many shortcut keys do you remember? Normally, … Continue reading

Rate this:

Posted in Excel 365 | Tagged | Leave a comment

Copy values from Status Bar in #Excel 365

You probably knew it… In Excel, when multiple cells are selected, we will see basic statistics of the selected cells in status bar: This is a very handy feature. But did you know… this handy feature has a nice touch … Continue reading

Rate this:

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

Workbook Statistics in #Excel 365

Whichever version of Excel you are using, you will see a lot of useful information about your worksheet/workbook in the Status Bar. Having said, many users are not aware of the presence of the status bar. Are you one of … Continue reading

Rate this:

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

Unhide multiple sheets with #Excel 365

Photo Hunt time – What are the differences between the two screenshots? Three, Two, One… Answer: Did you see it? The long-awaited feature is finally here in Excel 365. The next question is how?

Rate this:

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

Unselect selected cells with #Excel 365

First of all, I wish you a happy, healthy, and Excellent new year of 2023! Unselect selected cells Maybe you knew this already … we can select multiple cells/ranges by holding CTRL key. However, there was no way to unselect … Continue reading

Rate this:

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

Merry Christmas 🎄

Wishing you a Merry Christmas and a happy new year! BeExcellence !

Rate this:

Posted in General | Leave a comment

Basic Date Calculations – Power Query vs Excel Formula

It is quite common to perform date-related calculations when we work with Excel. Before Power Query, we must do it with built-in functions. However, we may not get the results we need with a single function most of the time. … Continue reading

Rate this:

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

Convert text date into date with Power Query for #Excel

I have a couple of blogposts about how to convert dates stored as text in various formats into real date that Excel recognizes, and you can further work on it. There are many approaches such as text to column and … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Extract content from a column in Power Query for #Excel

It is a common task to extract certain texts from a cell in Excel. For example, we may want to If you are good in Excel formula, you may be able to achieve this by constructing formula with Excel functions … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Power Query Challenge from Excel On Fire

Split columns dynamically I came across this challenge from Excel On Fire. This challenge just happened related to the topic of my previous post regarding splitting column using Power Query. So i take the challenge and offer a different solution. … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Split column in Power Query for #Excel

In regular Excel, Text to Columns is the non-formula approach to split contents from a column into multiple columns. It is a very powerful feature in Excel which many users are not yet aware of it. If you want to … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Merge Columns in Power Query for #Excel

Merging columns is a super easy task in Power Query. What we need to know is where to click. Having said that, we need to pay attention to The order of selecting columns to be merged, as it directly impacts … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment

Replace values in Power Query

Find and Replace is quite common in Excel. If you are not sure about what options we have for Find and Replace, please read my blogpost here. In regular Excel, the Find and Replace action is one-off. Whenever we have … Continue reading

Rate this:

Posted in Power Query | Tagged , | Leave a comment