STEP 4: Now we need to link them together! Go to PivotTable Tools > Analyze > Calculations > Relationships STEP 3: Click All in PivotTable Fields and you should see both tables there. Go to Insert > Pivot Table > New Worksheet Make sure to tick Add this data to the Data Model. Both tables are linked by the StudentId column. 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. 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. See that our years are now arranged in this order: 2013, 2014, 2012.ĭata Model and Relationships In Microsoft Excel Pivot TablesĮver 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!
STEP 2:This will sort our grand totals by descending order. (If you cannot see the Grand Totals, click in your Pivot Table and go to the ribbon menu and select PivotTable Tools > Design > Grand Totals > On for Rows and Columns) STEP 1:Right click on a Grand Total below at the bottom of the Pivot Table.