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 showed me the above layout and asked for the formula to SUM and AVERAGE the last 10 records of each player…… As he called me Excel guru, there is no reason I do not help him. 😁
For demonstration purpose, I change the last 10 records to 5. Indeed, I have assigned a variable (from 3-10) for user’s selection. The expected result is to obtain the SUM and AVERAGE of the highlighted cells, shown below:
And new records will come from time to time. That means the table will expand. The best way to deal with it is of course (not whole-column-referencing but) turning the data range into Excel Table.
You may download a sample file to following along.
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 the basic of Find and Replace, it requires no special reading as it is quite straight-forward. Perhaps the most practical tip to regular user is the shortcut keys:
Ctrl+F ==> Find
Ctrl+H==> Find and Replace
However, did you know… you can also
specify the search condition to look for “partial” or “exact” match?
ask for “case sensitive” search?
look into values only (not formula)?
even search for a format?
If you are eager to learn more about Find and Replace, and hence boost your productivity with Excel, please continue to read this article. 😉
As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course.
This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by MrExcel.com and ExcelIsFun channels. You may check it out here:
MrExcel solved the problem with VBA while ExcelIsFun solved it with LET functions. Super cool techniques. However it requires the latest version of Excel with Dynamic Arrays. What if you don’t have Dynamic Arrays? Power Query should be the best alternative. I was a bit surprised that they didn’t solve the problem with Power Query. So I couldn’t wait to try on my own and you know what… Yes, it can be solved with Power Query!
Excel MVP – Mynda Treacy offers a wide range of Excel course online @MyOnlineTrainingHub where you can learn practical Excel skills at your own pace.
And from now through August 20, there will be 20% off for both Excel Dashboards and Power BI courses. So what you are waiting for? 😉
Disclosure: I make a small commission (at no additional costs to you) for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.
It is a common task for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. 😁
Wait? What about if you want to combine all visible worksheets from all Excel files in a folder? It requires a little more Magic from Power Query but it’s totally achievable without deep-diving into M code… just touching the surface would do. 😉
Let’s watch it in action:
You may download the sample files HERE to follow along:
(note: suggest you put the Q4 file aside for “refresh” later)
Step-by-Step instructions with screenshots
If you prefer reading to watching, please continue to read the step-by-step instructions below.
I wonder… when you see this message, which button would you click? Please let me know by leaving comments below.
From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.
Why is that?
Because most of the time they don’t have access to a “workbook” that is saved in someone else PC. Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?
If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.
So, the question is:
Shall I link to external workbooks when writing Excel formula?
In Excel, it is a common (but not necessarily a good) practice for people to add a thin blank column in a table to give a visual effect of divider. Like the screenshot below:
To insert a blank column in Excel is super easy… probably the reason of making such practice a common one, i guess. But when you try to do that in Power BI Desktop, you will find it super hard. After a little of Googling, you still have no clue how to do that.
Indeed I did not and do not recommend my colleague to do so when he asked me to replicate a report from Excel to Power BI. I said “NO” many many times. And then one day, he sent me a Power BI page that shows blank columns as divider (see below) and kindly asked me to replicate that for him…
Well… I know I know, I haven’t written new posts for a while. I was lazy in previous weeks; enjoying summer breezes. You know what, if I were not doing Excel on weekends, I would probably have been enjoying hiking or camping. 😎😎
Following the techniques used in the previous post , we can perform many Excel magics for different scenarios. On top of my mind is to replace blank cells with zero.
The first task is to select all blank cells in the range. We can do so by using the following shorcuts:
Power Query is magically powerful, yet it is not well known to majority of Excel users. I think most Excel users are not even aware of its existence… So, let’s talk about how Power Query could solve a real-life problem commonly found at workplace. 😉
One day, I got an IM message from a colleague who attended a few of my in-house Excel Training courses… (I didn’t hold any Power Query session yet; so she has no idea of Power Query at all.) She asked:
If there is a quick way to combine (append) 100+ worksheets in a workbook.
I replied:
“Of course… only if you know how!” 😁 I was bad, I know.
I then asked her to share her workbook with all sensitive information removed.
Here’s the content of the worksheets (all data are fake… of course):
What she wanted to extract from the worksheets is the range highlighted above, with the metadata “Employee Name” and “Employee ID” being put together.
Here’s the layout of the expected outcome:
No empty columns; no empty rows… just a simple table!
The real challenge
You may be thinking… that’s easy… it can be done in just a minute. BUT remember this, there are more than 100 worksheets in the workbook and we need to do that to each worksheet.
Then we have to combine all transformed worksheets into a single table:
AND needless to say, we may have more worksheets (employees) in the workbook that we want to apply the same transformation and consolidation in the future.
If you have been following my blog for a while, you should probably know that I’ve started my YouTube channel about one year ago. Up to this moment, I’ve uploaded 54 videos. Almost one video per week. Not bad as a part-time YouTuber, I guess. 😅
And you know, most of the videos (in the Excel Magic in One Minute) are silence, without any voice over. For the rest, I recorded them in Cantonese, which is my mother tongue.
I know Cantonese is a very niche segment for Excel learning. And I know majority of YouTube viewers do not understand Cantonese. Therefore I put English subtitles on all my videos with Cantonese voice over. I planned to continue in this way until one day I read the following comment from bebuger bebuger:
Thank you very much for your kind words, bebuger! It’s very encouraging! 😉
With your encouragement, here comes my first video in English:
I hope you like it.
Here’s the Cantonese version:
It turned out that recording English Voice Over is much more efficient. For a 10-minute video, it took me 5+ hours in preparing the English subtitles. I had to write the script, put it onto the video, and sync it with the video. Indeed it’s a quite tedious and time consuming.
This time, I recorded the video in Cantonese first. After editing the video, I recorded English voice over on it. Although it took me many times for the recording… although my fluency and intonation was not good, I finished it in just more than one hour… Cool. 😎 I should have done this earlier.
If you like my videos, please give a thumb up 👍, share it and SUBSCRIBE. 😉
Thanks again, bebuger. I hope I will have more subscribers as a result.
Append Queries + Group By = Another alternative for a better Full Join
In the previous post, we explored an alternative way to perform FULL OUTER JOIN with Left Outer Join + Right Anti Join, followed by Append Queries in order to get a result with better layout. Well, as it’s Excel, that means there are different ways to achieve the same. In this post, let’s see how we can do the same using AppendQueries + Group By.
Again a picture tells thousand words. The following diagram illustrates the concept:
In the end of this post, I will post a video (work in progress 😅) riding on the example in previous post to demonstrate it. From which you will see the two different approaches yield the exactly the same result.
For illustration purpose, two simple tables are used for this post. Let’s see how to do it step by step in Power Query!
If you prefer reading to watching, please continue to read this post.
The situation:
I have two tables. One is Sales Table; One is Traffic Table. What we need to do is to combine them into a single fact table.
With Power Query, it is an easy job. We can merge the two tables with Left Outer Join!
BUT… If we do that with Left Outer Join, we will miss some data points. The following screenshot depicts the situation:
Those traffic records with “?” do not have a matching record in the Sales table. As a result, those traffic records are missed in the output, and most of the time without any notice/warning… 😑 Continue reading →
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. :)
Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity
