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.
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.
STEP 1: Select your first table. Go to Insert > Table. Click OK.
STEP 2: Go to Design > Table Name and give your new Table a descriptive name. In our example, we will name it Names
STEP 3: Select your second table. Go to Insert > Table. Click OK.
STEP 4: Go to Design > Table Name and give your new Table a descriptive name. In our example, we will name it Sales
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.
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.
For Excel 2010, go to PowerPivot > Create Linked Table.
STEP 7: This will open Power Pivot Window. Your two Tables should already be loaded there.
STEP 8: Go to Design > Create Relationship.
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.
For Excel 2010, you can do the same using the Create 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
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:
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!
