Excel Power Pivot: Create Relationships Between Tables

Charts & pivot tables · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, create a Power Pivot relationship by adding both tables to the Data Model, then using Power Pivot > Manage > Diagram View to connect their matching key columns, or by choosing Design > Create Relationship in the Power Pivot window. A relationship lets PivotTables and measures combine related tables without physically merging all their rows.

What a Power Pivot relationship does

A relationship tells Excel how rows in one Data Model table correspond to rows in another. For example, a Sales table can contain many transactions for each product, while a Products table contains one row per product. Linking the two tables by ProductID lets a report show sales alongside product names or categories.

The tables remain separate in the model. This is useful because a transaction table can grow over time while a smaller lookup table stores descriptive information. It also avoids repeating product descriptions on every sales row and makes it easier to build reports from several related sources.

Relationships are different from worksheet formulas such as XLOOKUP and from Power Query merges. A relationship connects tables in the Data Model; a lookup formula returns values into worksheet cells, while a merge combines query results into a new table. You can use Power Query to prepare tables and still relate them in the model afterward.

Prepare the tables and key columns

Before creating the link, check that both tables are in the Data Model and that the columns intended to match represent the same kind of identifier. A relationship depends on the values in those columns, not on similar column names. ProductID can match ItemCode if their stored values and meanings agree; two columns both named ID are not necessarily related.

  • Format each source range as an Excel table with a header row. Select a cell in the range and choose Insert > Table, or press Ctrl+T on Windows. On Mac, the shortcut is also usually Ctrl+T; menu availability can vary with keyboard settings.
  • Add both tables to the Data Model. One route is Power Pivot > Add to Data Model when the Power Pivot tab is available. For a Power Query result, use Home > Close & Load To, then select Add this data to the Data Model.
  • Use compatible data types for the two key columns. For example, a numeric product key in one table will not reliably match a text value such as 104 in the other.
  • Check the lookup table for duplicate or blank key values. The column on the one side must contain a unique value for each entity.

For a simple sales model, Products[ProductID] should have one distinct row per product, while Sales[ProductID] may repeat many times. If the products table contains duplicate ProductID values, remove or resolve the duplicates before attempting the relationship. Do not remove duplicates blindly if they represent meaningful separate records.

How to create the relationship

The following steps apply to Excel for Microsoft 365 on Windows. The Power Pivot add-in may need to be enabled first: choose File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and select Microsoft Power Pivot for Excel if it is listed. If your organization manages Office settings, the add-in or feature may be unavailable.

  1. Confirm that both tables are loaded into the Data Model. Select Power Pivot > Manage to open the model window.
  2. Choose Home > Diagram View in the Power Pivot window. The model’s tables appear as boxes with their column names.
  3. Find the matching key columns. Drag the key column from the table with unique values onto the corresponding key column in the table where values repeat. For example, drag Products[ProductID] to Sales[ProductID].
  4. Inspect the resulting line. It should connect the intended columns, with the one side at the lookup table and the many side at the transaction table.
  5. Alternatively, choose Design > Create Relationship in the Power Pivot window. In the dialog, select the table and column on each side, then confirm the relationship.

You can also create a relationship from Excel’s Data tab by choosing Relationships and then New, where that command is available. Select the tables and columns in the dialog and confirm. If the command is unavailable, make sure the workbook has tables in its Data Model and that you are using an Excel edition with Data Model relationship tools.

To check or edit existing links, open the Power Pivot window and use Design > Manage Relationships. In Excel’s worksheet window, Data > Relationships also opens relationship management in supported Windows editions. A PivotTable built from the Data Model can then use fields from both connected tables.

Choosing the correct relationship columns

Most reporting models use a one-to-many relationship. The one side is a lookup or dimension table with one row per item, customer, date, or other entity. The many side is a fact table containing events such as sales, orders, or visits. A customer can appear once in Customers and many times in Orders.

Choose a stable key rather than a descriptive label when possible. Product names can change, may not be unique, and can differ in punctuation or spacing. A product code designed to identify a product is generally a better relationship key. If a source lacks a suitable key, create or clean one in the data-preparation process rather than relying on approximate text matches.

Excel relationships use exact key matches; they do not perform fuzzy matching. Values such as 0012 and 12 may be different, especially when one is stored as text and the other as a number. Likewise, extra spaces or inconsistent capitalization can indicate data-cleaning issues. Standardize identifiers before loading the tables.

Concrete example: products and sales

Suppose Products has ProductID, ProductName, and Category, with one row for each product. Sales has OrderDate, ProductID, Quantity, and UnitPrice, with many rows for each product. Create a relationship from Products[ProductID] to Sales[ProductID]. The Products table is the lookup side; Sales is the many side.

When creating a PivotTable from the Data Model, put Category from Products in Rows and a sales amount measure or suitable numeric field from Sales in Values. The relationship allows the product category to filter the relevant sales rows. If you put ProductName next to a value from Sales and get missing or unexpected totals, first check that the relationship exists and that the key values truly match.

A relationship itself does not calculate revenue or change the source rows. It provides the path by which filters and fields can work together in the model. For a robust report, define measures for calculations such as total sales rather than assuming that every numeric column should be summed; quantity and unit price, for instance, need to be multiplied at the row level before being aggregated.

Common problems and how to fix them

  • The relationship will not save: Check for duplicate values on the intended one side, incompatible data types, or a key column that is not actually unique. Resolve the underlying data problem rather than selecting a different column simply to make the dialog accept the link.
  • The relationship line appears reversed: Review which table has unique keys. The table with one row per key belongs on the one side; repeated keys belong on the many side. The line’s markers help show the cardinality.
  • Some rows do not contribute to a report: Look for unmatched keys, blanks, leading or trailing spaces, inconsistent text-versus-number storage, or records absent from the lookup table. A relationship cannot invent missing lookup records.
  • A PivotTable shows a blank category: This can occur when fact-table keys have no matching row in the lookup table. Find those orphan keys and correct the source or add valid lookup records.
  • The Data Model tools are missing: Confirm that you are working in Excel for Windows and that the Power Pivot add-in is available and enabled. Power Pivot authoring is not available in Excel for Mac or Excel for the web in the same way as the Windows desktop application.

When troubleshooting, inspect a few actual key values in each table and verify that they match exactly. Also check whether the report uses the intended field from the lookup table rather than a similarly named field from the fact table. A valid relationship can still produce confusing output if the PivotTable fields are chosen incorrectly.

Managing multiple relationships and model design

A model can contain several tables joined through keys, such as Sales to Products, Sales to Customers, and Sales to Dates. For clarity, use a central fact table and lookup tables with descriptive attributes. This star-shaped design is often easier to understand and report from than a web of repeated tables and ambiguous links.

If two tables have more than one meaningful connection—for example, Orders has both OrderDate and ShipDate linked to a Dates table—decide which date should filter reports by default. Excel Data Models allow only one active relationship between a given pair of tables at a time; an additional relationship can be inactive where supported and used in a DAX measure with USERELATIONSHIP. If you do not need that distinction, a single date relationship may be simpler.

Many-to-many situations need particular care. A direct relationship requires a unique key on the one side, so two tables that both repeat the linking value cannot simply be joined as though one were a lookup table. A common modeling pattern is a bridge table containing one row per distinct key, with one-to-many relationships from the bridge to each of the other tables. The right design depends on what each row represents and how totals should filter.

Power Pivot relationships versus merges and formulas

Use a relationship when you want separate tables in one Data Model and need PivotTables or measures to combine their fields. It can reduce duplicated descriptive data and supports a model that can be refreshed from multiple sources. The relationship does not create a flattened worksheet table.

Use a Power Query merge when you specifically need to join columns into a query result, filter or transform records during preparation, or produce one combined output table. Use a worksheet lookup when the goal is to return a value into cells for ordinary sheet calculations and the data volume or reporting needs do not call for a model. These approaches can coexist: Power Query can clean or merge source data, and the Data Model can relate the resulting tables.

Frequently Asked Questions

Can Power Pivot create a relationship between two Excel tables?
Yes. Add both tables to the Data Model, then create the link in Diagram View by dragging matching key columns or use Design > Create Relationship in the Power Pivot window. The lookup-side key must be unique.

Do the column names have to be identical?
No. The names can differ. What matters is that the columns contain corresponding identifiers in compatible formats, such as the same ProductID values.

Why does Excel say the relationship cannot be created?
Common causes include duplicate keys on the one side, incompatible data types, or selecting columns that do not represent the same key. Check uniqueness and sample values in both tables, then correct the source data if needed.

Can I create a Power Pivot relationship on a Mac or in Excel for the web?
Full Power Pivot model authoring and relationship management require Excel for Windows. Excel for Mac and Excel for the web do not provide the same Power Pivot tools, so use the Windows desktop version to create and manage these model relationships.

Does a relationship merge the tables?
No. It connects tables inside the Data Model, leaving them separate. Use Power Query’s merge operation if you need a combined query table, or a worksheet lookup if you need returned values in cells.

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.