Create a Relationship to build a Pivot Table
In Excel 2013 and 2016/365 you can now create a relationship between tables of data. This is
a feature that can be used to pull data from the different tables to build a PivotTable.
The first step in this process is to format the data/table with the Format as Table feature.
This can be found on the Home tab, as part of the Styles group.
Once you have formatted the table you need to give it a name. Naming the tables will make it
easier to identify and work with your data in the Pivot Table. Third, you will need to setup a
relationship between each of the three tables.
In the following scenario, using hotel data (you as the employee), have downloaded new data into Excel from
the hotel’s SQL database. You have downloaded three tables that you would like to use in a pivot table.
These three tables are; Customers, Reservations and Rooms tables.
As mentioned above, to be able to build a pivot table from all three tables you will need to prepare the data
first. The first step is to format each table as a table using the Format as Table feature. Second name each
table with a unique name. Third, setup a relationship with each table using the Relationship feature on the
Data ribbon.
After the above three steps are done, you will need to use of Excel’s Business Intelligent the PowerPivot
add-in. If it is not currently one of your tabs in Excel, you will need to active this add-in.
(Click File, Options, Add-ins category. At the bottom
of the dialog box click the down arrow for Manage
and select COM Add-ins and click the Go button.
In the Add-ins available list check the box for Microsoft Power Pivot for Excel. Then click OK.)
Using the Power Pivot ribbon you add each table to your Data Model. After all are added, click Manage, Click
Pivot Table on the Home ribbon.
Building a pivot table using multiple tables
1. Click in the Customers table
2. On the Home ribbon, click the Format as Table button and select Blue, Table
Style Medium 6.