Multi-Table PivotTables with Data Model

1. The Concept of Relational Data

Before we dive into the "how," let's understand the "what." The Data Model works because it understands relational data structures. This typically involves two types of tables:

• Fact Tables: These contain records of transactions or events. For example, a table of every single sale made. This table can have many repeating values (e.g., the same customer ID appears every time that customer makes a purchase).
• Dimension Tables (or Lookup Tables): These contain unique lists of descriptive information, like a list of customers, products, or store locations. Each item (e.g., each customer ID) appears only once.

The two tables are linked by a common column, often called a key. This creates a one-to-many relationship: one customer in the dimension table can be linked to many sales in the fact table.

The most important rule for this to work is that the key column in the dimension (lookup) table must not contain any duplicate values. Let's see what happens when this rule is broken.

2. Building a PivotTable with the Data Model

There are a couple of ways to create a PivotTable using the Data Model. We'll explore two primary methods. First, a quick method where Excel prompts you to create relationships, and second, a more structured approach where you define the relationships upfront.

Method 1: Adding to the Data Model During PivotTable Creation

This is a fast way to get started. The key is a checkbox in the Create PivotTable dialog.

This method is great for simple models, but for more complex reports, it's better to define your relationships proactively.

Method 2: Defining Relationships First

This more robust method involves telling Excel how your tables are related before you even create the PivotTable. This is the recommended approach for building clear and maintainable data models.

Let's look at another excellent resource that demonstrates this workflow.

For those who prefer a written guide, the following article walks through the exact same process with detailed screenshots.

3. Managing and Visualizing Your Data Model

Once you've created relationships, you have a "Data Model" living inside your workbook. You can manage and visualize this model using the Power Pivot window.

To access this, go to the Data tab and click Manage Data Model. If this is your first time, Excel may ask you to enable the add-in.

The Power Pivot window has two main views:

• Data View: Looks like a spreadsheet, showing the data in each table.
• Diagram View: This is the most useful view. It displays your tables as boxes and the relationships as lines connecting them. You can see the "one" ($1$) and "many" ($\text{*}$) sides of each relationship.

This diagram is not just for viewing; you can also create, edit, or delete relationships by dragging and dropping fields between tables right here.

4. Hands-On Practice

It's time to build your own PivotTable from a Data Model. Create a new Excel workbook and follow these steps.

Step 1: Create Your Data Tables

Create three separate worksheets named Sales, Customers, and Products. Enter the following data on the corresponding sheets.

On the Sales sheet:

On the Customers sheet:

On the Products sheet:

Step 2: Convert to Excel Tables

For each of the three data sets:

1. Click anywhere inside the data.
2. Press Ctrl + T.
3. Ensure "My table has headers" is checked and click OK.
4. Go to the Table Design tab and give each table a logical name: tblSales, tblCustomers, and tblProducts.

Step 3: Create the Relationships

1. Go to the Data tab -> Data Tools group -> Relationships.
2. In the Manage Relationships dialog, click New....
3. Create the first relationship:
   • Table: tblSales
   • Column (Foreign): CustomerID
   • Related Table: tblCustomers
   • Related Column (Primary): CustomerID
   • Click OK.
4. Click New... again to create the second relationship:
   • Table: tblSales
   • Column (Foreign): ProductID
   • Related Table: tblProducts
   • Related Column (Primary): ProductID
   • Click OK.
5. You should now see both relationships listed. Click Close.

Step 4: Create the PivotTable

1. Go to Insert -> PivotTable.
2. In the Create PivotTable dialog, select the option Use this workbook's Data Model.
3. Choose to place it on a new worksheet and click OK.
4. In the PivotTable Fields list, you'll see all three tables.
5. Build the report:
   • From tblProducts, drag Category to the Rows area.
   • From tblCustomers, drag Region to the Columns area.
   • From tblSales, drag Quantity to the Values area.

You have now successfully created a PivotTable that summarizes data from three separate tables!

Conclusion

Congratulations, you have unlocked one of the most powerful features in Excel for data analysis. By moving away from manual VLOOKUP consolidations and embracing the Data Model, you can build reports that are faster, cleaner, and infinitely more scalable.

Key Takeaways:

• The Data Model allows you to create relationships between tables instead of merging them.
• Data is typically organized into Fact Tables (transactions) and Dimension Tables (lookups).
• Relationships are usually one-to-many, and the key column on the "one" side must have unique values.
• You can create relationships proactively via Data > Relationships and then build a PivotTable from the workbook's Data Model.
• The Power Pivot Diagram View is the best place to visualize and manage your data model.

In our next lesson, we will build upon this foundation. Now that you can construct sophisticated reports from multiple tables, we will explore how to add your own custom formulas directly into your PivotTables using Calculated Fields and Calculated Items.