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 with formula in a helper column? What we want to achieve is to create a flag for each transaction indicating which brands are present in the transaction. The screenshot below demonstrates what we want to achieve. (Please read previous post to understand more about the challenge.)
Honestly, if I am not using #Excel 365, I would not do this with formula as it is too challenging. Nevertheless, with the amazing new functions and dynamic array in #Excel 365, this can be accomplished with a formula as simple as this:
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 in the screenshot above, all items purchased within the same transaction will be displayed on multiple lines, with the same transaction ID. For example, Transaction T001 contains four different items and hence occupies four lines of record; Transaction T004 contains only one line of record as there is only one item sold in that transaction. Of course, this is a simplified example.
Now the question is, how many transactions are there with just Brand A and Brand B together? How many sales generated from those transactions? etc….
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. Doable but not efficient at all as it is the old school method. 😁
With the introduction of UNIQUE function, doing a distinct count with formula becomes easy. Look at this:
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, even for a power user of Excel, we just memorize those actions we perform frequently. For a rarely used action, we click this and that tab to browse for it, most of the case. Right?
The Right-Click ‘Search the menus’ comes to rescue
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 up in Excel 365? By a simple click, the values on status bar will be copied to clipboard for pasting to other applications where you need it!
Let’s watch it in action:
Isn’t it nice?
Do you like this little touch up? Please share with us by leaving a comment below!
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 them?
Today, I want to highlight the “Workbook Statistics” which is available in Excel 365.
Simply click on it, then it will open the following dialog box:
As you see, you can see the key statistics of the current sheet and the current workbook. I like the first info – End of sheet the most. Why? Because it is always the first thing I investigate when someone called me asking “Why is my file size so big when I have only a few data points?”. 🤔
Before the “Workbook Statistics”, I need to ask the user to press Ctrl+End which I found challenging sometime (coz many people don’t know what Ctrl+End does, and they would press Ctrl+N instead) 😅.
Tip: The "Workbook Statistics" can only be found under "Review Tab". Currently available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2021
What? You are using Excel 365 but couldn’t find “Workbook Statistics” on the status bar? No worries! Right click anywhere on Status Bar and you can customize what info to be displayed on it.
This is a small feature, but I like it. What do you think? Do you think it is helpful? Please leave your comment below!
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 selected cells. No more an issue with Excel 365. Watch the following:
Hey, this is more than just for drawing. If you have tried selecting multiple ranges of same size for copying, you know how frustrating it was when you selected one cell more at the last range accidentally. You know that feeling? 😁😅 No more with Excel 365!
Are you ready for the journey to Excel 365
Are you using Excel 365? If you are, do you know there are many nice improvements in it?
A short story
Recently, my company got updated to Excel 365 and (I think) I was the only person who felt excited about it. Indeed, some colleagues would prefer to go back to Excel 2016 as they are having some performance issues with Excel 365 (probably because we are using 32-bit version of Excel 365 😅).
That’s a common response for each version update. If you have been using Excel for long enough, do you recall the general feedback from users when Excel 2003 was updated to Excel 2007? In my memory, most people complained about it as there was a major revamp of the user interface and more. People got frustrated before they realized the enhancements that made their work lives easier. Since Excel 2007, Excel has had an updated version every three years. We have Excel 2010, 2013, 2016 and 2019… In recent years, Excel 365 (always updated, sort of) is getting popular. It will become the prevailing version of Excel in the future as there will be updates every month. It will be too long for a three-year window to get updated in this digital era. If you are interested in catching up with the monthly updates of Excel, visit this Excel Blog from Microsoft.
I am still catching up with all the splendid features in Excel 365. I would love to share some practical tips with you in my blog here. Let’s start with a simple but nice feature as demonstrated above.
I will show more bites-size tips and functions for Excel 365 in coming posts. Stay tuned!
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. In fact, a combination of functions as well as simple math are required. With Power Query, we can perform many date-related calculations via User Interface of Power Query Editor. In this blog post, I am going to show you both the Power Query way (in video) and the Excel formula way (text).
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 formulas. You may refer to these posts.
These are examples that most Excel users deploy. Did you know… there is a powerful tool in Excel that can make this task super easy! Yes, that’s Power Query!
It is a common task to extract certain texts from a cell in Excel. For example, we may want to
get the length of the cell content,
extract the first or last N characters,
extract the text in a range,
or even a portion of text that is before/after or even in-between specific delimiters.
If you are good in Excel formula, you may be able to achieve this by constructing formula with Excel functions such as LEN, LEFT, MID, LEFT, etc. For simple scenario, it works fine. Nevertheless, let’s face the fact that not everyone is good at writing Excel formula. Also, we must admit that there are some scenarios where only “complex” formula can help.
Unbelievably, with Power Query we don’t need to write a single formula to extract values from a column under many different circumstances. Most of the tasks can be done via User Interface.
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.
Don’t miss the video from Excel On Fire. Oz demonstrated many different Power Query techniques to solve the problem, with his unique way of storytelling! I love that!
And this is my solution to the same problem using different approach. The core techniques involved are splitting column into rows and then pivot the resulting column. And the challenge is to keep the records with no item and make the solution dynamic to handle new data in the future.
I hope you like it. Do you have a different solution? Please feel free to share in the comments below.
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 learn more about Text to Columns, click here.
In Power Query, the Split Column is even more powerful, yet handy!
Note: Split Column is also available under Home tab of the Power Query Editor
As you can see the from screenshot above, there are seven ways to split a column (if you are not using an old version of Excel).
By Delimiter
By Number of Characters
By Positions
By Lowercase to Uppercase
By Uppercase to Lowercase
By Digit to Non-Digit
By Non-Digit to Digit
Note: Depends on the version of your Excel, you may not have options 4 to 7.
In this post, I will walk you through the 7 options to spilt a column. If you prefer watching video instead of reading, please go to the end of the post.
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 new data, we need to perform the same action, repeatedly. If we want to do that by a click of refresh, then we should do it in Power Query. Find and Replace is called “Replace values” or “Replace errors” under the Transform tab of Power Query Editor.
As the name suggests, we can replace values or errors in Power Query. To perform Find and Replace in Power Query is similar to doing the same thing in Excel. However, there are a few details we need to pay attention to.
The above image is not surprising at all. SUMIF returns the expected result correctly. We all write SUMIF like this. (Yes, me too. 😅) It works fine most of the time unless we are facing something not so common. Say for example, the text strings all start with “>> “, like the screenshot below:
To be clear, the content in A3 is equal to the content in D2, however the SUMIF is returning the wrong result? Strange…… 🤔
Do you know what’s wrong with the SUMIF formula? It is the same as the SUMIF formula in the first example. Then why does the first one work, and the second one fails? That’s the question!
If you want to know just the quick fix, here’s the formula:
=SUMIF(A2:A4, "=" & D2, B2:B4)
'The secret sauce is "=" &
If you want to know why, you need to know the basics of writing SUMIF.
In regular Excel, all the above Power Query transformations can be done with simple functions, except for TRIM which Power Query does it slightly different from the regular Excel. You may read the previous post for more details.
You may see the corresponding Excel functions in the following table:
Power Query
Regular Excel function
lowercase
UPPERCASE
Capitalize Each Word
Trim (only remove leading and trailing spaces)
Clean
Add Prefix
Add Suffix
LOWER
UPPER
PROPER
TRIM (also remove extra spaces between words)
CLEAN
“Prefix” &
& “Suffix”
You probably know all these Excel functions and wonder why we need to do it in Power Query. I had the same questions too when I first started with Power Query. If you try to clean / transform your data in Power Query, you will find it makes perfect sense NOT to do it with regular Excel functions on worksheet. We shall do all sorts of transformations in Power Query Editor. And most of the time, text transformations stated above are only part of the data cleansing that we need. Therefore, it is important for beginners of Power Query to be aware of their existence in Power Query. And the best thing is we, as users, can do it through merely the User Interface of Power Query Editor, i.e. with a few clicks.
Let’s watch it in action how we can do these text transformations with Power Query:
In Power Query, there are some functions that are very similar to Excel, which you expect the same results from them. However, it may not be the case because they indeed perform differently. TRIM is one of them.
TRIM is a common function in Excel that removes all leading and trailing spaces in a text string. On top of that, TRIM also removes all extra space in between texts. As a result, only single spaces between texts will be kept. Below screenshot shows the results returned by TRIM.
When we perform Transform –> Trim or use the Text.Trim function in Power Query, the results may be a surprise to you! (At least it surprised me when I first noticed it). Here’s the result from Power Query:
Whenever we see “strange” results, we should always check the definition of the function. Indeed, we should do so before we use a function (in the ideal world). But I admitted that I did not do so either. 😅
From the MS documentation, I realized that the Text.Trim function removes leading and trailing spaces only. That’s why.
Unfortunately, there is no such function as Text.TrimLikeExcelin Power Query. But you know, when we work with Excel, there will be workaround(s), most of the time. We may achieve the same with a few Power Query functions together, i.e a little bit of M.
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
Wanna buy me a drink!?
Click below photo to my PayPal.Me
I appreciate it. :)
wmfexcel 