When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data.

What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales Table.

Linking Excel Tables 01

What we want to know is how much each Employee made in Total Sales. 

You can see that each employee is uniquely identified by the ID number, which is also used in the Sales table.

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Select your first table. Go to Insert > Table. Click OK.

Linking Excel Tables 02

 

STEP 2: Go to Design > Table Name  and give your new Table a descriptive name. In our example, we will name it Names

Linking Excel Tables 03

 

STEP 3: Select your second table. Go to Insert > Table. Click OK.

Linking Excel Tables 04

 

STEP 4: Go to Design > Table Name  and give your new Table a descriptive name. In our example, we will name it Sales

Linking Excel Tables 05

 

STEP 5: Select your first table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 06

 

STEP 6: Select your second table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 07

For Excel 2010, go to PowerPivot > Create Linked Table.

Excel 2010 - Create Linked Table

 

STEP 7: This will open Power Pivot Window. Your two Tables should already be loaded there.

Linking Excel Tables 08

Linking Excel Tables 09

 

STEP 8: Go to Design > Create Relationship.

Linking Excel Tables 10

 

STEP 9: Ensure for Table 1, you set Names = ID and for Table 2, you set it to Sales = ID.

This will set the relationship and your Sales table will be able to see the values in the Names table.

Linking Excel Tables 11

For Excel 2010, you can do the same using the Create Relationship Dialog Box:

Excel 2010 - Relationship Dialog Box

 

STEP 10: With this, our setup is complete.  Now it’s time to create a Pivot Table to do our analysis.

Within the PowerPivot Window, go to Home > PivotTable

Select New/Existing Worksheet and press OK

Linking Excel Tables 12

 

STEP 11: This will create a new Pivot Table within your Excel worksheet.

In the ROWS area put in the Name field from the Names Table,  in the VALUES area you need to put in the Sales Amount field from the Sales Table:

Linking Excel Tables 13

 

STEP 12: We now have the Names and the Total Sales Amount all in one Pivot Table.

We were able to link and consolidate two Excel Tables together with no need for VLOOKUP or helper columns…thanks to Power Pivot!

Linking Excel Tables 14

Linking Excel Tables

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Importing Excel Workbooks in Power Pivot  Power Pivot is a very powerful analytical tool which allows you to import data from various external sources!This opens up many possibilities and gives you the power to do further data analysis and get insightful business metrics.You can import data from the fol...
Enabling Power Pivot in Excel 2016 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.Read the tutorial on how to install Power Pivot in Excel 2010 hereRead the tutorial on how to enable Power P...
Installing Power Pivot in Excel 2010 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.Read the tutorial on how to enable Power Pivot in Excel 2013 hereRead the tutorial on how to enable Power Pivot ...
Using Excel Tables in Power Pivot To be able to use Power Pivot, you need to have data imported into the Power Pivot Window first.If your data is in the normal Excel worksheets, then you will not be able to use this data in Power Pivot.Fortunately there are a lot of possible ways Power Pivot can retrieve ...