Pivot table from multiple tables practice workbook

Download the Pivot Table Practice Workbook

Practice on the sales table and the customer and article lookup tables from the video. Connect them with relationships and build both reports yourself.

The Two Reports We Want to Build

The objective is to create two Pivot Table reports; one for sales by customer, the other for quantity sold by article description.

Two pivot table reports: sales by customer and quantity sold by article, both sorted descending

The Source Data: One Sales Table and Two Lookup Tables

We have our source data in the following tables.

The main table (fact table) containing the daily sales information.  This is a proper Excel Table named “TableSales”.

Excel table TableSales with sales document, date, customer code, article code, quantity and sales value

Two additional Excel Tables that are used as lookup tables, “MasterArticle” and “MasterCustomer” will translate a Customer Code and an Article Code into meaningful information for the reports.

The Old Way: Merge Everything with VLOOKUP

To create a Pivot Table used to mean combining all the relevant information into a single table.  Using a function like VLOOKUP, we would create a merged table that looked like the following table.

Old method: one merged table with article and customer details added through VLOOKUP

The Better Way: Connect Tables with Relationships

The better way to “blend” the data is to not blend it at all.

We leave the three tables in their original form. We use Relationships to connect the tables by some common field.

Diagram linking TableSales to MasterCustomer by customer code and to MasterArticle by article code

As seen above, the “TableSales” and “MasterCustomer” tables share a common field, the Customer Code field.  The “TableSales” and “MasterArticle” tables share a common field, the Article Code field.

When we build the Pivot Table, we can add a field such as Sales Value from the “TableSales” table, and the Customer Name field from the “MasterCustomer” table.

The sale for a transaction will be cross-referenced to the “MasterCustomer” table to translate the Customer Code to a Customer Name.

If your data sits in separate files instead of separate sheets, combine multiple Excel files with Power Query first.

How to Create Relationships Between Excel Tables

Step 1: Format Each Range as an Excel Table

To connect tables using relationships requires that each table be “upgraded” from a plain table to a proper Excel Table.

Select each table and press CTRL-T.  In the Create Table dialog box, ensure the proper data range is selected and the “My table has headers” option is selected.  Do this for each table.

Create Table dialog with My table has headers checked

Step 2: Give Each Table a Clear Name

Although optional, it’s a good idea to give the newly upgraded tables better names.  This will make creating the relationships (next step) easier and more understandable.

Select a table and click Table Design (tab) -> Properties (group) -> Table Name.  Give the table a name of your choosing (ex: “MasterArticle”).

Note: table names can’t contain spaces. They must start with a letter, an underscore or a backslash.

Table Design tab with the table name set to MasterArticle

Repeat this process, giving each table a more meaningful name.

Step 3: Create the Relationships

To create the relationships, select Data (tab) -> Data Tools (group) -> Relationships.

Relationships button in the Data Tools group on the Data tab

In the Manage Relationships dialog box, select New.

Manage Relationships dialog with the New button highlighted

In the Create Relationship dialog box, select the “TableSales” and “MasterArticle” tables along with the common field Article Code as the field that will link the two tables together.

One of the two columns must hold unique values, like a customer code that appears only once in the customer table. Both columns also need the same data type.

Create Relationship dialog linking TableSales to MasterArticle on Article code

Once created, the link appears in the Manage Relationships dialog box.

Manage Relationships showing the active TableSales to MasterArticle relationship

Repeat this process to create the link between the “TableSales” and “MasterCustomer” tables using the Customer Code field as the link field.

Create Relationship dialog linking TableSales to MasterCustomer on Customer code
Manage Relationships showing both active relationships

Click Close to exit the relationship building step.

Create a Pivot Table from the Data Model

Insert the Pivot Table

To create the first Pivot Table report that focuses on Customer Names and Sales, select an existing sheet or start a new sheet, then select Insert (tab) -> Tables (group) -> PivotTable.

PivotTable button on the Insert tab

In the Create PivotTable dialog box, ensure the selection for “Use this workbook’s Data Model” is selected.  You can also decide the exact location of the pivot table.

Create PivotTable dialog with Use this workbook’s Data Model selected

Add Fields from Different Tables

To add the fields to the report structure, expand the table names in the PivotTable Fields panel (right) to reveal all available fields.

PivotTable Fields pane listing MasterArticle, MasterCustomer and TableSales
PivotTable Fields pane with all three tables expanded

Place a check in the box next to Customer Name (“MasterCustomer” table) and Sales Value (“TableSales” table).

Customer Name and Sales Value checked from two tables, creating the first report

Format and Sort the Report

A few more tweaks to the Pivot Table to get the report just the way we want.

  • Customize the headings to read “Customer Name” and “Sales Value”
  • Sort the list in descending order by “Sales Value”
  • Apply a Currency style number format to the “Sales Value” field
Finished pivot table with customer names and sales values sorted largest to smallest

For more ways to clean up a report, see these pivot table tips and tricks.

Build the Second Report

Perform the steps outlined above with the following differences:

  • Place checkmarks in Quantity (“TableSales” table) and Article Description (“MasterArticle” table)
  • Customize the headings to read “Article Description” and “Quantity”
  • Sort the list in descending order by “Quantity”
  • Apply a Number style number format to the “Quantity” field
Second pivot table showing quantity sold by article description

See Your Relationships in Diagram View

When working with many tables connected via relationships, it is helpful to see the tables and their connections graphically.

To see this view, select Data (tab) -> Data Tools (group) -> Manage Data Model.

Manage Data Model button in the Data Tools group on the Data tab

This launches the Power Pivot for Excel window.  In this window, select Home (tab) -> View (group) -> Diagram View.

Diagram View button on the Home tab of the Power Pivot window

This view displays all tables and their connecting links.

Power Pivot Diagram View showing the three tables and their relationships

Hide Key Fields from the Field List

When working with multiple tables, common fields between tables will be listed twice.  If these fields have the same names, it can become confusing to the viewer as to which should be added to the Pivot Table.

Field list showing customer code and article code listed twice across tables

This confusion can be reduced by hiding fields that will not be used in reports.

Key fields (the columns that link the tables) are rarely needed in a report. Hide them in every table where they appear.

While in the Diagram View (data model), right-click a field you wish to hide and select “Hide from Client Tools”.

Right-click menu in Diagram View with Hide from Client Tools selected

Repeat this process until all unneeded fields have been hidden.  The hidden fields take on a faded appearance to indicate their hidden status.

Hidden key fields shown faded in Diagram View

Once the Data Model has been closed, a cleaner, less confusing list is presented to the viewer.

Cleaner PivotTable field list without the key fields

Download the Pivot Table Practice Workbook

Get the Pivot Table Practice Workbook

Build one pivot table from three tables, without a single VLOOKUP.

Frequently Asked Questions

Can you create a pivot table from multiple sheets in Excel?

Yes. Format each range as an Excel Table, connect the tables with relationships on the Data tab, then insert a pivot table and keep the Data Model option selected. One pivot table can then use fields from all connected tables.

How do I create a relationship between two tables in Excel?

Go to Data, Relationships, New. Pick the main table and its linking column, then the lookup table and its matching column, and click OK. Repeat for every lookup table.

Why is the Relationships button grayed out?

The workbook needs at least two Excel tables. If your data sits in plain ranges, select each range and press Ctrl+T to turn it into a table first.

Why can Excel not create my relationship?

One of the two columns must contain unique values, such as a customer code that is listed only once in the customer table. Both columns also need compatible data types, and the lookup column can have no more than one blank.

Do I still need VLOOKUP to combine tables for a pivot table?

No. Relationships let one pivot table use fields from several tables, so you can leave the tables as they are. Power Query is only needed when the data has to be cleaned or reshaped first.

How do I hide fields from the PivotTable field list?

Go to Data, Manage Data Model, switch to Diagram View, right-click the field and choose Hide from Client Tools. The field no longer shows up in the PivotTable Fields pane.

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.