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!
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.
First of all, I wish you a happy, healthy, and Excellent new year of 2023!
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 using Excel 365? If you are, do you know there are many nice improvements in it?
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!
Wishing you a Merry Christmas and a happy new year! BeExcellence !
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).
You may download the sample file to follow along.
Let’s begin with Power Query. See how easy it is in the following video:
Want to know the Excel formula equivalent? Keep reading.
Continue readingI 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!
Continue readingIt 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 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.
Continue reading
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!
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).
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.
You may download a sample file to follow along.
New to Power Query, please read this to start with.
Continue reading
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
You may download a sample file to follow along:
Continue readingFind 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.
Continue readingSUM is probably the most frequently used function in Excel. And probably the first function we learned. Though powerful, it cannot handle errors… 😰
This is the fact we need to deal with. SUM cannot handle errors. AGGREGATE comes to rescue.
Continue reading
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.
Continue reading
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:
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.TrimLikeExcel in 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.
When we import data from a data source, be it a data warehouse, a CSV/TXT file, or even an Excel Table, there are chances that there are many more columns than needed for analysis. This is particularly true when we try to create a fact table with only “keys” and “fact” in the table. Luckily, we can easily remove columns that we don’t need, or simply keep those columns we need with Power Query.
Let’s watch it in action!
You may download a sample file to follow along:
In Power Query Editor, there is a button of “Keep Duplicates” in the ribbon. It’s located under Keep Rows in the section of Reduce Rows in the Home tab of the ribbon.
With this, it is super easy to keep duplicates in our dataset. By the way, duplicates mean value appears more than once. Let’s take a look at the screenshot below, “A”, “B”, and “F” are duplicates,
while “b”, “C”, “D”, “E” are uniques (i.e. values appear only once).
When we apply “Keep Duplicates” to Column1, we obtain the following result.
The good thing about Power Query is, we do not have to worry about the M code auto-generated. Power Query handles the M codes, we handle the User Interface, which is mainly “select and click”. Easy for users, isn’t it?
When we import data into Power Query editor, we should set the scope of data that we need. That means selecting only the rows and columns required for further processing. In the following video, I will show you different ways of keeping the rows that you need with Power Query.
You may download the sample file to follow along.
Cantonese version:
Let’s think about this scenario. In the first encounter of Power Query, you were presented with demonstrations that show all the powerful transformations done by Power Query. You were surprised and amazed with the demos. Normally we did not pay attention to subtle details about the powerful tools. We jumped straight into all the transformation steps by following examples. Sometimes it works, something it doesn’t. And then you feel frustrated as you don’t know why some calculations give you unexpected results. If this sounds familiarize, I will say, this is because we are all trying to run before walk. 😅
In this blogpost, and in the video, I will talk about a basic but crucial step in Power Query – Data Type.
Formatting does not change underlying values, defining data type in Power Query does.
When we work with data using Power Query, defining data type of each column before loading the result is a best practice. Please don’t mix it up with formatting in Excel. Defining data type to a column is fundamentally different from applying a formatting. In Excel, when we apply a format to a number, the underlying value of the number remains unchanged. On the contrary, when we define data type of a column in Power Query, all the values under the column will be converted to the data type that’s being defined.
Nevertheless, defining data type in Power Query is not for data conversion only. We need the data type to be explicitly defined before some calculations happen. Otherwise, we may have unexpected results returned. The worst scenario is we don’t even know that. For example, when you try to add “10” to 10, Excel returns 20 while Power Query returns error. Similarly, when you try to add 10 to an empty cell, Excel return 10 but Power Query returns null. In general, number does not work with text; text does not with number in Power Query.
Continue reading
New Generation Finance - Accounting - Controlling using Microsoft BI stack
A Power BI Creator Blog
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Peltier Technical Services - Excel Charts and Programming
Turn your data into opportunity
Q&A about Excel
Doug Glancy's Excel Site
wmfexcel 