Create a Pivot Table Based on Multiple Sheets / Tables

Have you ever needed to make a Pivot Table where the source data was spread across multiple tables?

Traditionally, Pivot Tables required all data used to be present in a single table. This meant combining multiple tables into a single table by either manually blending the data (this is the worst way) or by implementing some form of lookup operation, either a VLOOKUP or INDEX/MATCH procedure.

Either way, extra work is extra work. It’s not what we want to spend our time doing.

Now there’s a better way. We will leave the original tables untouched and connect them using Relationships.

The Objective

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

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 Days” Method

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 “Modern” Method

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.

Building Relationships

Upgrading the Source Tables

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.

Naming the Tables

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 cannot contain spaces and MUST begin with a letter.

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

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

Once created, the link appears in the Manage Relationship dialog box as follows.

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.

Creating the Pivot Table Reports

Create the Table Structure

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.

Populating the Table Structure

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

Customizing the Pivot Table 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

Creating 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

Viewing the Data Model

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.

Hiding “Unnecessary” Fields

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.