Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields?
I was so lost when I saw this from a workbook on my friend’s notebook. He came to me for PivotTable help but I was not able to even work with the PivotTable he opened and presented to me… How embarrassing… 😑😅
It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…
Can it be done?
Of course it can be done. BUT we could do it doesn’t mean we should do it.
My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.
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.
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…