Data Model and Relationships in Microsoft Excel Pivot Tables

If you ever need to create a report by connecting multiple relatable tables in only one Pivot Table then you can use the Data Model and Relationship in Excel Pivot Tables. 

We are going to use our data in which we need to create a report. In report, we will be showing the First Name of students and the number of classes of those same students. The main part is that these both are in different classes one is in student table and another one is in classes table.

Previously In Pivot Tables: How to Multi-Select Slicer Items in Microsoft Excel Pivot Tables?

All the steps are mentions below: 

STEP1. Make sure to select the First Name table and then follow this:

Go to Insert > Pivot Table > New Worksheet

Then click OK after ticking the Add this data to the Data Model.

Data Model and Relationships

STEP2. Follow this procedure after selecting the id table.

Go to Insert > Pivot Table > New Worksheet 

Then click OK after ticking the Add this data to the Data Model.

Data Model and Relationships

STEP3. You can see both the tables thereafter clicking all in Pivot Table Fields.

Data Model and Relationships

STEP4. Now link them together by following this:

Go to PivotTable Tools > Analyse > Calculations > Relationships

Data Model and Relationships

Now click on new option.

Data Model and Relationships

STEP5. Whenever you want to link a relationship it has two sides which need to be linked.

You have to make sure that the primary table has no duplicates. It does not have duplicate student Ids because it is a student table. Now set these followings and then click OK afterward. 

Table – Range

Column – Range1

Related Table – First Name

Related Column – Id

Data Model and Relationships

Now click the close option.

Data Model and Relationships

STEP6. Put in the (first name) filed in the Rows section and then put in the (id) field in the Values section.

Data Model and Relationships

After completing the sixth step you will see the results in a merged fashion.  

This information will not be easily available from the individual tables.