Power Pivot takes the power of relational database queries to your desktop. Only requirement is Excel 2010 or 2013. And knowing how to use it of course…
Important: This post is not explaining all aspects of power pivot and detailing all of it’s uses. This post is a tutorial about making simple relational pivot tables.
Most of the time you keep seperate data tables for different but related data sets. When you need to make analysis with the data from two of these tables, things gets messy. You porbably make a third table that contains necessary columns from both tables and make your analysis using that table.
You can define relations between separate tables with one click and then create pivot tables that can use fields from all these tables at the same time, all thanks to power pivot.
For example, you keep a table to track sales and another one to track customer information. You want to see from which products are sold to which city. Sales table has item group data and customer table has city data. No table has both data. You need to make a 3rd table with both data than make a pivot table out of it? Not if you have power pivot on your disposal.
When you enable power pivot in Excel (Options/Add-ins) you will have a ribbon that looks like below.
Click on any cell in your product list and from PowerPivot ribbon, click “Create Linked Table” button. This will turn your list into a table (if not already so), then open power pivot window and create a table for your products. Do the same things for your customers and sales lists/tables.
You can see that power pivot window is similar to normal excel interface. But it’s use (formulas,etc.) is a bit different. This will be covered in another post.
When you have all your tables created as linked tables, it is time to define their relations. Click on the “Diagram View “button on the ribbon to the right. You will get an interface showing your tables as floating lists, showing their columns.
All you need to do to define relations is clicking on one item in a list and drag a line from it on to same item in another list. Here, click on “Item Code” in sales and drag the line on to “Item Code” in products table. Now these two tables are linked.
Now do it with “Customer ID” field for Sales and Customers tables. Now all three tables are linked.
It’s time for pivot table action. Click on the “Pivot Table” button to create one. You will get a pivot table field list like the one below:
Nice isn’t it? Now we can do all sorts of cross analysis. Lets see some examples:
Distribution of quantity sold for products by city:
Sales revenue for each city:
Which customer spent how much money for which manifacturer’s which product.
Examples can be multiplied, but I belive we have covered what Power Pivot is made for.
Power pivot is available for Excel 2010 (as an add-in) and Excel 2013 (built-in). If you are an Excel 2010 user, you can get it from here.
Once you download it, you can get it running by activating it from options/add-ins.
You can also download the example workbook for this tutorial for you to practice on power pivot.