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 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 “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.
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.
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.
This confusion can be reduced by hiding fields that will not be used in reports.
It is common practice to not use “key” fields (the fields used to link tables) in reports. These can be hidden in all tables where they occur
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.
Feel free to Download the Workbook HERE.
Learn Excel from a Microsoft MVP
Check out my bestselling Excel Courses
Learn anytime that fits your schedule.
Download files. Practice. Apply.