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.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
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
Featured Course
Power Excel Bundle
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.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
