Microsoft launched PowerPivot as a separate add-in for Excel 2010. And since then it has become an integral part of any data analyst’s toolkit. The immense “power” of this tool comes from the fact that it can aggregate data from related but distinct tables into one pivot table.
Don’t worry if you’re slightly confused, Oz du Soleil is here to demonstrate what this tool can do for you. Please note that the explanation uses Excel 2013.
Get ready to be amazed!
1 – Enable PowerPivot
Here are the steps you need to follow to enable this built-in add-in:
- Go under the ‘FILE’ tab and click on ‘Options’.
- Now choose ‘Add-Ins’ from the left panel.
- From the dropdown menu at the bottom, select ‘COM Add-ins’ and click on Go.
- Tick the PowerPivot checkbox and click on OK.
And you’re done. A tab saying ‘POWERPIVOT’ should appear on your ribbon.
2 – The Data
Let’s say the data we have looks something like the picture shows. One can easily spot the relationships right away. Each sales rep had an accompanying assistant given in a separate table. And each assistant, in turn, reports to a different manager given in the third table.
What if you wanted to see how much revenue was brought in by each of the managers? There are two ways to solve this:
- By creating a complicated set of formulas involving VLOOKUPs and SUMIFs, or
- By using PowerPivot to elegantly solve the problem.
Well, we will use the latter approach.
3 – Creating Relationships
This is where we tell Excel the link between seeming disparate tables. Go under the ‘Data’ tab and click on ‘Relationships’. Follow it by clicking on ‘New’. Now we create the relationship between Sales Rep from the SALES table with that in the ASSISTANT table, as shown in the picture below:
To complete the task click on OK. You would be able to see the link now within the previously empty list. We also add the link between Assistant from ASSISTANT table with that from the MANAGER table.
4 – Creating (Power) PivotTable
Within the ‘PowerPivot’ tab, click on ‘Manage’. This takes you to your Data Model in a new window. Within the ‘Home’ tab in the new window, click on ‘Diagram View’. This view shows all your tables, with their respective links, which are currently part of your Data Model. Click on any one of the links to see its visualization.
To insert a pivot table that brings together or aggregates the data from these three tables, click on ‘PivotTable’ under the ‘HOME’ tab. Insert it wherever you want. Go to the ‘ANALYZE’ tab on your spreadsheet window and enable ‘Field List’ if it is not already enabled.
In order to see revenue against the managers, we put Manager from MANAGER table in the ROWS section. And we put Price from SALES table in the VALUES section. The resulting table is shown in the picture below.
You could also look at the revenue by assistants or see the breakdown of revenue into the three products by manager. The possibilities are astounding and PowerPivot helps you get to them in a matter of seconds.
What’s next?
Go on and explore PowerPivot, get your colleagues and friends onboard as well. If you deal with problems of summarizing multiple, related tables, you can says hours of effort with this tool.
And do not forget to share your experiences with us in the comments section below.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
