Free Practice WorkbookEver had multiple related tables and wondering how to create a report that connects them together in a single Pivot Table? We have just the thing with Data Model and Relationships!
Key Takeaways
-
Combine Multiple Tables with Ease – The Excel Data Model lets you connect multiple tables without merging them into one, using defined relationships.
-
Relationships Mimic Database Joins – You can create relationships between tables using common fields (like IDs), similar to how foreign keys work in databases.
-
Power Pivot Unlocks More Power – With Power Pivot, you can build complex data models, create calculated columns and measures using DAX, and manage large datasets efficiently.
-
No Need for VLOOKUP – Instead of using formulas to combine data, relationships let Pivot Tables pull from multiple tables directly, simplifying analysis.
-
Improves Data Integrity & Scalability – The Data Model approach keeps tables clean, separate, and scalable, which is ideal for reporting and future updates.
Table of Contents
Our Data Setup
This is our data that we will use. What we want to do is create a report that shows the First Name of the student and the Number of Classes that the student has taken.
The tricky part here is the First Name is in the Students Table, while the number of classes can be retrieved from the Classes Table. Both tables are linked by the StudentId column.
Data Model and Relationships In Microsoft Excel Pivot Tables
STEP 1: Select the Classes Table. Go to Insert > Pivot Table > New Worksheet
Make sure to tick Add this data to the Data Model. Click OK.
STEP 2: Select the Students Table. Go to Insert > Pivot Table > New Worksheet
Make sure to tick Add this data to the Data Model. Click OK.
STEP 3: Click All in PivotTable Fields and you should see both tables there.
STEP 4: Now we need to link them together! Go to PivotTable Tools > Analyze > Calculations > Relationships
Click New.
STEP 5: There are 2 sides of a relationship when we want to link them together.
The rule of thumb, is the primary table should have no duplicates. This is the Students table as it does not have duplicate Student Ids.
Set the following then Click OK.
Table – Classes
Column – StudentId
Related Table – Students
Related Column – StudentId
Click Close.
STEP 6: In the ROWS section put in the Students(FirstName) field. In the VALUES section put in the Classes (ClassName) field.
With just that, you can see that Excel was able to show the results in a merged fashion! You can see Daisy has 2 classes enrolled. And from the individual tables, you would not have that information readily available!
Frequently Asked Questions
What is the Data Model in Excel?
It’s a built-in relational data engine that lets you connect and analyze data from multiple tables in one Pivot Table.
How do I add tables to the Data Model?
While inserting a Pivot Table, check the box “Add this data to the Data Model”, or use Power Pivot to import and manage tables.
Can I build relationships without Power Pivot?
Yes! In Excel 2013 and later, you can create relationships directly via Data > Relationships, even without using Power Pivot.
How do relationships improve my Pivot Tables?
Relationships let you analyze fields from different tables together without needing lookup formulas or manual joins.
Is the Data Model available in all versions of Excel?
The Data Model is available in Excel 2013 and later, including Excel for Microsoft 365. Power Pivot tools may require enabling the add-in.
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.
30 Days - Full Access 
