Pinterest Pixel

Linking Excel Tables in Power Pivot

Bryan
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.

We have a specific example for you using a Name Table and a Sales Table.

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. We have a specific example for you using a Name Table and a Sales Table.

Key Takeaways:

  • Establish Relationships Between Tables – Power Pivot allows you to link multiple tables using relationships, eliminating the need for VLOOKUP and improving data efficiency.
  • Use a Common Key for Linking – To create a relationship, ensure that both tables have a common key, such as an ID or unique identifier, to properly connect data.
  • Create Relationships in Diagram View – The Diagram View in Power Pivot provides a visual way to drag and connect tables, making it easier to manage relationships.
  • Enable Efficient Data Analysis – Linking tables in Power Pivot allows you to analyze large datasets dynamically without needing to merge them into a single table.
  • Use DAX for Advanced Calculations – Once tables are linked, you can use DAX (Data Analysis Expressions) to perform advanced calculations across related tables for deeper insights.

The Data Setup

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

Linking Excel Tables in Power Pivot

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.

How to Link Excel Tables in Power Pivot

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

Linking Excel Tables in Power Pivot

 

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 in Power Pivot

 

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

Linking Excel Tables in Power Pivot

 

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 in Power Pivot

 

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 in Power Pivot

 

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 in Power Pivot

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

Linking Excel Tables in Power Pivot

 

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

Linking Excel Tables in Power Pivot

Linking Excel Tables in Power Pivot

 

STEP 8: Go to Design > Create Relationship.

Linking Excel Tables in Power Pivot

 

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 in Power Pivot

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

Linking Excel Tables in Power Pivot

 

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 in Power Pivot

 

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 in Power Pivot

 

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 in Power Pivot

Frequently Asked Questions

How do I link two Excel tables in Power Pivot?
To link two tables, open Power Pivot, go to the Manage window, and navigate to the Diagram View. Drag and drop a common key (such as an ID column) between the tables to establish a relationship.

Why is the ‘Create Relationship’ button grayed out in Power Pivot?
The button may be grayed out if your tables are not formatted as Excel Tables (using Ctrl + T) or if you’re working with incompatible data types. Ensure both columns you want to link are of the same data type.

Can I create multiple relationships between two tables in Power Pivot?
No, Power Pivot allows only one active relationship between two tables. However, you can create multiple inactive relationships and use the USERELATIONSHIP DAX function to switch between them when needed.

What happens if I delete a relationship between linked tables?
If you delete a relationship, any calculations or PivotTables relying on that connection will return errors or incorrect results. You may need to re-establish the relationship and refresh your data.

Can I link more than two tables in Power Pivot?
Yes, you can create relationships between multiple tables, forming a data model. This allows you to build more complex reports and PivotTables without duplicating data across tables.

PIVOT BANNER

If you like this Excel tip, please share it



Linking Excel Tables in Power Pivot | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Format Text Using Power Query or Get & Transform

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...