Beyond the Basics Part Three: Linking Two Tables Using PowerPivot

What Is a PivotTable?

A PivotTable is a data-processing tool used to query, organize, and summarize data or information between spreadsheets, tables, or databases. Drag and drop fields into a PivotTable to use the data as row labels, column labels, or summarized data.

Part Three: Linking Two Tables Using PowerPivot

Adding the First Table

1. The data must first be converted to a table. Click on one cell within the table, press Ctrl + T, and then press Enter.

2. Click Design → Properties → Table Name and type a name for the table.

3. Repeat steps 1 through 3 for the second dataset.

4. Click on a cell in the first table.

5. Click PowerPivot → Tables → Add to Data Model.

6. PowerPivot opens in a new window.

Note: The table name is on the tab in the lower-left corner, with a link

icon to show it is linked. The number of records also appears in the status

bar at the bottom.

Adding the Second Table

1. Click on the second table.

2. Click PowerPivot → Tables → Add to Data Model.

3. The table name appears on a second tab in PowerPivot.

4. Click Home → View → Diagram view. Resize as needed.

Create Relationships

1. Drag a relationship field in the first table to the desired relationship field in the second table. Or click Design → Relationships → Create Relationship.

2. A line appears connecting the two tables. Repeat for as many tables/relationships as necessary.

3. Click Home → PivotTable. Click OK. The PivotTable opens back in Excel.

4. The two tables appear in the PivotTable Fields panel.

Note: Traditional PivotTables do not allow

for multiple tables in the field panel.

5. Expand the tables by clicking the arrow to the left of the Table name.

6. Create the desired PivotTable.

TOOL TIP: With the PowerPivot Add-in, quickly analyze data models with over 100 million rows.

---

---