Excel Pivot Table Relationships: How to Link Data

Charts & pivot tables · Updated November 18, 2026

Excel PivotTable relationships connect separate tables through matching columns in the workbook’s Data Model, so you can summarize related information in one PivotTable. In Excel for Microsoft 365 on Windows, add the tables to the Data Model, create a relationship between their key columns, and then build the PivotTable from that model.

What a PivotTable relationship means

A relationship tells Excel how rows in one table correspond to rows in another. For example, a Sales table might contain one row per order line and a Product ID for each item. A Products table can contain one row per product, along with its name and category. A relationship between the two Product ID columns lets a PivotTable combine sales amounts from Sales with product categories from Products.

The relationship is part of the Data Model, not a formula that copies values between worksheet cells. The source tables remain separate. Excel uses the relationship when it evaluates fields placed in a PivotTable, including when it groups or filters the results.

This is different from making two ordinary PivotTables on separate sheets. Those PivotTables do not become related simply because they use similar column names or sit in the same workbook. To analyze columns from distinct tables together, the tables generally need to be in the Data Model and connected by a valid relationship.

A practical example: sales and products

Suppose the Sales table has Order ID, Product ID, Quantity, and Line Amount. The Products table has Product ID, Product Name, and Category. Product ID is repeated in Sales because a product can appear on many order lines, but it should appear only once in Products.

With a relationship from Products to Sales, you can place Category in PivotTable Rows and Line Amount in Values to compare sales by category. You can also add Product Name to Rows or use it as a filter. Excel gets the descriptive product fields from Products and the amounts from Sales, matching records by Product ID.

Relationships are most useful when tables represent different kinds of information, such as transactions and the customers, products, dates, or locations associated with them. Keeping those subjects in separate tables avoids repeatedly storing descriptive details in every transaction row and makes the model easier to maintain.

When relationships are useful

Use a relationship when the data is already organized into multiple tables and a report needs fields from more than one of them. A typical design has a fact table containing events or transactions and dimension tables containing descriptive details. A fact table might record one row per sale; a customer table might provide one row per customer.

Relationships can help when you need to:

  • Summarize a large transaction table by labels stored in a separate lookup table.
  • Reuse a customer, product, or date table across several transaction tables in one model.
  • Keep source data in a normalized structure instead of merging every description into every transaction row.
  • Build a PivotTable with fields from multiple related tables without adding worksheet lookup formulas.

If all the relevant fields are already in one clean table, a conventional PivotTable is usually simpler. Relationships are not a requirement for every PivotTable, and adding a Data Model can introduce extra design and troubleshooting work when the data does not need it.

Prepare the tables and key columns

Before creating a relationship, make each source range an Excel Table. Select a cell in the range, choose Insert > Table, confirm whether the table has headers, and give it a clear name on the Table Design tab. Tables expand more reliably as new rows are added, and their names make the model easier to understand.

Choose the columns that identify matching records. The column on the lookup side must have unique values: in the example, each Product ID should occur once in Products. The corresponding Product ID can repeat in Sales. This is the common one-to-many pattern: one product record relates to many sales rows.

  • Check that the key columns use compatible data types. A number stored as text may not match the same-looking numeric value.
  • Remove unintended duplicates from the lookup column. If one Product ID has two product records, Excel cannot treat that table as the unique side of the relationship.
  • Look for blanks and inconsistent key values, such as extra spaces or different codes for the same item.
  • Use a single key column for a relationship. Excel relationships do not join tables using a multi-column key directly.

For a genuine many-to-many situation, such as students enrolled in multiple courses, a bridge table can represent the pairings: one row for each student-course combination. The exact model depends on the data, but simply joining two tables where both key columns contain duplicates is not a sound substitute for defining the relationship structure.

Create a relationship in Excel for Windows

Excel’s Data Model is available in the Windows desktop version of Microsoft 365. Add each table to the model, then connect the matching columns:

  1. Select a cell in the first table and choose Insert > PivotTable. In the PivotTable creation dialog, select the option to add the data to the Data Model, if shown, and create the PivotTable. This adds that table to the model.
  2. Repeat for other tables, or use the Power Pivot tab’s Add to Data Model command if that tab is available. The Power Pivot add-in may need to be enabled in Excel options.
  3. Choose Data > Relationships, then select New in the Relationships dialog.
  4. Choose the lookup table as the Table and the unique key as its Column. Choose the related table and its matching key in the Related Table and Related Column fields.
  5. Confirm the relationship. If Excel reports an issue, check that the lookup column is unique and that the columns have compatible data types.

Excel may detect relationships automatically in some circumstances, but automatic detection is not a guarantee that the intended relationship exists or is correct. Review the model before relying on a report. The Power Pivot window’s diagram view can also help show which tables are connected.

Build a PivotTable from the related tables

Once the tables are in the Data Model and connected, create a PivotTable using the model as its source. Depending on the dialog and workbook state, choose Insert > PivotTable and select the workbook Data Model, or create the PivotTable while adding the source to the model. The PivotTable Fields pane should list fields from the model’s tables, usually under their table names.

Drag fields from the relevant tables into Rows, Columns, Values, and Filters. For the sales example, put Products[Category] in Rows and Sales[Line Amount] in Values. Excel will aggregate the amount for sales associated with each product category. If Excel puts a numeric field in Rows instead of Values, or chooses an unexpected summary, use the field’s menu in the layout pane to change its placement or value-field settings.

A relationship makes fields available together; it does not determine what the report should measure. Check the aggregation and the level of detail. Summing a transaction amount is often appropriate, while summing a customer ID is usually not. If a report needs a distinct customer count or a more specialized calculation, a Data Model measure may be more appropriate than the default summary.

Common problems and how to diagnose them

The relationship cannot be created: The most common cause is duplicate values in the column intended for the lookup side. Identify which table should contain one row per key, then remove or resolve duplicates there. Also check for mismatched data types and blank keys.

A PivotTable shows a blank category: Some rows in the related transaction table may have keys that do not exist in the lookup table, or the keys may differ because of spelling, spaces, or type differences. Excel can group unmatched records under a blank item. Compare the source key values and correct or account for missing lookup records.

Fields from a table are missing: Confirm that the table was added to the Data Model and that the PivotTable uses that model, rather than a single worksheet range as its source. Also check whether the field list is showing the table you expect.

Totals seem too high or repeated: Check the grain of each table and the uniqueness of the lookup key. A relationship does not deduplicate transaction rows. If the supposed lookup table contains repeated keys, or if the source transaction table already has duplicate records, a total may not mean what you intended. Verify the source rows and the model design before changing the displayed aggregation.

New records are missing: Refresh the source connection or PivotTable after updating the source data. Excel Tables usually expand when new rows are entered directly below them, but confirm the table range and refresh the report. Refreshing data does not repair incorrect keys or create a missing relationship automatically.

Relationships versus merging tables

A relationship keeps tables separate in the Data Model and connects them by key. A merge, such as a merge operation in Power Query, combines columns from one query into another before the result is loaded. Neither approach is universally better.

Relationships suit a reusable model with separate facts and lookup tables, especially when multiple reports need the same descriptive tables. A merge can be convenient when the final output should be one flat table or when the source layout needs transformation. Merging can also duplicate rows if the matching column is not unique on the lookup side, so it still requires attention to keys and table grain.

Ordinary worksheet lookup formulas are another option for bringing descriptions into a single table. They can be suitable for a small or simple report, but they create and maintain values in the worksheet rather than defining a reusable relationship in the Data Model. Choose based on the shape of the data and the reporting need, not on the assumption that every workbook needs a relational model.

Platform and compatibility considerations

The instructions here target Excel for Microsoft 365 on Windows, where the Data Model and Power Pivot relationship tools are available. Excel for Mac and Excel for the web do not provide the same full Data Model authoring experience as Windows desktop Excel. If you need to create or change relationships, use supported Windows desktop Excel and check that the recipient’s version can work with the model before sharing the workbook.

A workbook that contains model-based PivotTables may behave differently in an environment with limited Data Model support. Do not assume that opening a workbook in a browser or on another platform will allow every model operation, refresh, or edit. For shared reporting, test the actual workbook workflow with the versions and data connections that recipients will use.

Frequently Asked Questions

Can a PivotTable use data from two worksheets?
Yes, if the tables are added to the Data Model and connected with a relationship. Two separate worksheet ranges do not become related just because they are in the same workbook.

Does a relationship copy data between tables?
No. It defines how Excel matches rows when analyzing fields in the Data Model. The original tables remain separate unless you separately transform or merge them.

Can I create a relationship when both key columns have duplicates?
Not as a standard one-to-many relationship between those tables. The lookup side needs unique key values. For many-to-many data, consider whether a bridge table can represent the associations.

Why does Excel show a blank item in my PivotTable?
There may be unmatched keys in the related table, blank keys, or values that look alike but differ in type or content. Check that every relevant transaction key has a corresponding lookup record.

Do I need Power Pivot to create relationships?
In Excel for Microsoft 365 on Windows, the Data Model and Data > Relationships provide the core relationship workflow. Power Pivot adds tools for working with and viewing the model, but the tab may need to be enabled and is not available in the same way on every platform.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.