Pinterest Pixel

Data Model and Relationships In Microsoft Excel Pivot Tables

Ever had multiple related tables and wondering how to create a report that connects them together in... read more

Download Excel Workbook
Bryan
Posted on

Overview

Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline

Ever 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!

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

Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline

Download excel workbookData-Model.xlsx

 

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.

Data Model and Relationships In Microsoft Excel Pivot Tables

 

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.

See also  Group By Half Years With Excel Pivot Tables

Data Model and Relationships In Microsoft Excel Pivot Tables

 

STEP 3: Click All in PivotTable Fields and you should see both tables there.

Data Model and Relationships In Microsoft Excel Pivot Tables

 

STEP 4: Now we need to link them together! Go to PivotTable Tools > Analyze > Calculations > Relationships

Data Model and Relationships In Microsoft Excel Pivot Tables

Click New.

Data Model and Relationships In Microsoft Excel Pivot Tables

 

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

Data Model and Relationships In Microsoft Excel Pivot Tables

Click Close.

Data Model and Relationships In Microsoft Excel Pivot Tables

 

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!

See also  Predetermined Number Formatting in Excel Pivot Tables

Data Model and Relationships In Microsoft Excel Pivot Tables

 

Further Learning:

Helpful Resource:

Data Model and Relationships

 

 

If you like this Excel tip, please share it
Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline
Data Model and Relationships In Microsoft Excel Pivot Tables | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

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