Microsoft Excel · Advanced modeling and platform-dependent features · Intermediate
Build Data Model relationships and a multi-table PivotTable
Relate a unique product lookup to transaction rows and build a multi-table PivotTable without duplicating source data.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Add both source tables to the Data Model, create a relationship from the transaction key to the lookup’s unique key, then put lookup labels and transaction amounts in the same PivotTable. Check subtotals, not just the grand total, to verify that filtering travels through the relationship.
Requirements and complete fixture
Use English Excel for Microsoft 365 desktop on Windows with the Power Pivot tab already available. These instructions do not claim Mac or browser equivalence or describe enabling an unavailable add-in. Start a disposable XLSX workbook; no external data connection is required.
On a sheet named Sales, enter A1:C5 and make it an Excel table named Sales:
Order ProductID Amount
O1 P1 10
O2 P2 20
O3 P1 15
O4 P3 5
On a sheet named Products, enter A1:B4 and make it a table named Products:
ProductID Category
P1 Office
P2 Kitchen
P3 Office
ProductID is Text in both tables. Sales contains repeated product keys because a product can appear in several transactions. Products must contain one nonblank row per key. Its two Office rows are valid because Category is not the key. The Amount total is 50.
Add the tables and relationship
- Select a cell in Sales and choose Power Pivot > Add to Data Model.
- Return to Excel and repeat for Products. Adding a table to the model does not merge its columns into the other table.
- In Excel, choose Data > Relationships > New.
- Set Table to Sales and Column (Foreign) to ProductID. This is the many side.
- Set Related Table to Products and Related Column (Primary) to ProductID. This is the unique lookup side.
- Confirm and inspect the relationship. Compatible data types and unique lookup values are requirements, not optional cleanup.
You can also inspect the model in Power Pivot > Manage. In Diagram View, a one-to-many relationship connects the tables. Use the relationship details when verifying the actual columns; a visible connector alone is not a substitute for checking which fields it relates.
Build a multi-table report
In the Power Pivot window, use Home > PivotTable, choose a new worksheet, and confirm. In the field list, put Products[Category] in Rows and Sales[Amount] in Values. Summarize Amount by Sum.
The report must show Office=30 and Kitchen=20, with grand total 50. Office combines O1=10, O3=15, and O4=5 across two different products. This is the benefit of the model: the category label comes from Products without copying it into every Sales row.
Filter the report to Office. The visible amount should be 30, not the full 50 repeated under every category. Clear the filter before the next test.
Test source changes and failures
Change Sales!C5 from 5 to 8. Update/refresh the model from the source table and refresh the PivotTable. Office becomes 33, Kitchen stays 20, and the grand total becomes 53. Check these numbers after the refresh has completed.
If every category shows the same grand total, inspect the relationship and whether the PivotTable uses the model tables you intended. If a lookup key is duplicated, correct the lookup according to its meaning rather than forcing an arbitrary relationship. If a sales key has no matching product, investigate the unmatched record; do not silently discard its amount to make categories look tidy.
Save as XLSX, close, and reopen. Verify the two model tables, relationship, and 33/20 subtotals. For ordinary single-table source and refresh issues, see PivotTable basics; this tutorial’s distinct outcome is reporting across related tables.