Power Pivot Table (Excel 2010)

Power Pivot is a free add-in to the 2010 version and can be downloaded from below link. It comes with Office 2013 Pro & Office 365.

Download Link: https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Power Pivot extends capabilities of the pivot data summarization and we can import data from multiple data sources.

The following free Microsoft Virtual Academy course can be very helpful in learning Power Query & Power Pivot along more info.

http://www.microsoftvirtualacademy.com/training-courses/faster-insights-to-data-with-power-bi-jump-start

In this post, I will share a basic use of Power Pivot with example in Excel 2010.

Once we have downloaded the add-in, we would be able to see it in our workbook in top ribbon.

With Power Pivot, we can link our multiple tables through the common Key/Column between them. It’s just like the relational database. Once we have linked them, then they could be summarized at one place. We will see it in this post step by step.

We have the below 3 tables of data in the 3 separate sheets.

Customer Table

KeyCustomer	Name	Gender	Type	State/Province
306256	Mary	Female	Individual	Punjab
306257	Sally	Female	Individual	Sindh
306258	Asif	Male	Individual	KPK
306259	Arif	Male	Individual	Punjab
306260	Nadeem	Male	Individual	Sindh
306261	William	Male	Company	KPK
306262	Michael	Male	Company	Punjab
306263	Natasha	Female	Individual	Sindh
306264	Praveen	Female	Individual	KPK
306265	Nazar	Male	Company	Punjab

Product Table

ProductKey	P-Name	Price	Size
P-123	T-Shirt	350	Large
P-124	T-Shirt	300	Medium
P-125	T-Shirt	250	Small
P-126	Hockey Stick	800	Large
P-127	Hockey Stick	750	Medium
P-128	Hockey Stick	700	Small
P-129	Tennis Racket	1000	Large
P-130	Tennis Racket	950	Medium
P-131	Tennis Racket	800	Small
P-132	Bag	1000	Large
P-133	Bag	950	Medium
P-134	Bag	800	Small

Sales Table

SalesID	Date	KeyCustomer	ProductKey	Quantity
S-111	12/1/2014	306256	P-123	2
S-112	12/1/2014	306257	P-124	3
S-111	12/1/2014	306258	P-125	1
S-112	12/1/2014	306259	P-126	4
S-111	12/1/2014	306260	P-127	5
S-112	12/1/2014	306261	P-128	3
S-111	12/1/2014	306262	P-129	2
S-112	12/2/2014	306263	P-130	6
S-111	12/2/2014	306264	P-131	9
S-112	12/2/2014	306265	P-132	1
S-111	12/3/2014	306261	P-133	2
S-112	12/10/2014	306262	P-134	2
S-111	12/15/2014	306263	P-126	2
S-112	12/16/2014	306261	P-127	3
S-111	12/17/2014	306262	P-128	1
S-112	12/18/2014	306263	P-129	4
S-111	12/19/2014	306265	P-131	5
S-112	12/20/2014	306261	P-132	3
S-111	12/21/2014	306262	P-133	2
S-112	12/22/2014	306263	P-131	6
S-111	12/23/2014	306261	P-132	9
S-112	12/24/2014	306262	P-133	1
S-111	12/25/2014	306263	P-133	2
S-112	12/26/2014	306259	P-134	2
S-111	12/27/2014	306260	P-125	2
S-112	12/28/2014	306261	P-126	3
S-111	12/29/2014	306262	P-127	1
S-112	12/30/2014	306263	P-134	4
S-111	12/31/2014	306264	P-126	5
S-112	1/1/2015	306265	P-127	3

Have your data in table form, for this select your data and press CtrlL. Name your table, just for clarity. For this have your cursor somewhere in table and go to design tab and change the default table name there.

After that click on the PowerPivot which would be at the top bar. Your cursor be in somewhere the table i.e Customer Table and then click on Create Linked Table. This will move the table in Power Pivot window. Do this for all the tables and move the 3 tables.

Now Go to Design Tab in Power Pivot window and then click on Create Relationship, a new window will open.

Relate the Product and Sales table on ProductKey Column and Customer and Sales Table on KeyCustomer Column, one by one.

This way all the three tables are linked now.

For any change in relationship click on Manage Relationship

To view the Diagram View of relationships, go to Table Tools tab and then click on Diagram View.

Once the relationship is finalized, go to workbook, PowerPivot then PivotTable and Create Pivot table as we do, but here we can have data from all the 3 tables in our Pivot table.