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.

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”.

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.

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.

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.

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.

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.

In the Manage Relationships dialog box, select New.

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.

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

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


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.

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.

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.


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

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

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

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.

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

This view displays all tables and their connecting links.

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.

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”.

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

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

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.









