Creating Linked Tables in Excel Using Power Pivot

Creating Linked Tables in Excel Using Power Pivot

Working with Microsoft Excel to produce multiple tables that are linked together is easy. This is made possible by the Power Pivot Feature. Linked tables can be displayed within only one view thanks to Pivot Table. For this demonstration, we will only be focusing our attention to two tables, namely the Name and Sales Tables. We will need to determine each Employee’s contribution in the Total Sales. A unique ID number is assigned for each and every employee.

STEP 1: Under the Insert menu and choosing Table, highlight your first table.

STEP 2: Rename your table by going to Design menu and choosing Table Name. We will be using Names for this example.

STEP 3: Under the Insert menu and choosing Table, highlight your second table.

STEP 4: Rename your table by going to Design menu and choosing Table Name. We will be using Sales for this example.

STEP 5: While your intended table for first is selected, under the Power Pivot menu, choose Add to Data Model. The table is then included in the Power Pivot Window.

STEP 6: While your intended table for second is selected, under the Power Pivot menu, choose Add to Data Model. The table is then included in the Power Pivot Window. Excel 2010 users can access the PowerPivot menu and choose Create Linked Table for this.

STEP 7: Doing so opens the Window for the Power Pivot with your imported tables.

STEP 8: Choose Create Relationship under the Design menu.

STEP 9: Enter Names = ID for Table 1 and Sales = ID for Table 2. Excel 2010 users can access the Dialog Box for Create Relationship.

STEP 10: The setup is now complete. For our analysis, access Home and then select PivotTable within the Power Pivot Window. Choose New or Existing Worksheet. Click Ok to confirm.

STEP 11: Within this newly-opened Pivot table, place the Names from the Table in Rows Section and place the Sales Amount from the Table in Values section.

STEP 12: The Names with the amount of Total Sales are now displayed in the same Pivot Table.

As a finished product, we have merged and linked together two different Excel Tables.