Excel Power Pivot relationships connect tables in the Data Model through matching columns, so you can analyze related data together without combining it into one worksheet table. In Excel for Microsoft 365 on Windows, create one from Data > Relationships > New, choosing the matching columns in the two tables; the lookup table’s relationship column must contain unique values.
What a Power Pivot relationship does
A relationship tells Excel how rows in separate Data Model tables correspond. For example, a Sales table might have a ProductID for every transaction, while a Products table has one row per product. Linking those ProductID columns lets a PivotTable show sales by product name, even though the name is not repeated in every transaction row.
The relationship does not physically merge tables or copy lookup values into the transaction table. Instead, Excel uses the link when it evaluates PivotTable fields and calculations. The tables remain separate, which can reduce duplicated data and make a model easier to maintain.
Power Pivot is Excel’s data-modeling technology. The Data Model stores tables and their relationships in a workbook, and Power Pivot provides tools for managing that model and creating DAX calculations. A relationship is foundational: it gives Excel a route for applying a selection in one table to relevant rows in another.
How relationships work: lookup and data tables
The most common arrangement is a one-to-many relationship. A lookup table, sometimes called a dimension table, has one row for each item, such as a product, customer, store, or date. A data table, often called a fact table, has many rows that refer to those items, such as sales transactions.
- Lookup side: Products[ProductID] contains one distinct, non-duplicate identifier per product.
- Data side: Sales[ProductID] may repeat because many sales can involve the same product.
- Relationship: Products[ProductID] links to Sales[ProductID], allowing product fields to help summarize sales.
In a PivotTable, you could place Products[Category] in Rows and a sales amount from Sales in Values. The relationship lets Excel calculate sales for each category. Without the relationship, Excel sees separate tables and cannot reliably interpret the category as describing the sales rows.
Relationships use columns, not a visual match between table names or worksheet positions. The linked columns should represent the same kind of key and contain compatible data types. A text ID such as “0017” is not necessarily equivalent to the number 17; formatting that makes values look alike does not always make their stored values match.
Requirements and limits to know
For the usual one-to-many relationship, the column on the lookup side must be unique. If a Products table contains two rows with ProductID 104, Excel cannot use that column as the “one” side. Remove or resolve duplicate keys, or redesign the model so the lookup table has a single row per key.
Power Pivot relationships in Excel are based on a single column. If the real-world match depends on two fields, such as StoreID and ProductID together, Excel cannot define a relationship directly on that pair. A common remedy is to create a combined key in both tables in the source data or in Power Query, taking care to use an unambiguous construction so different pairs cannot accidentally produce the same text.
A direct many-to-many link is not the standard relationship pattern in Excel’s Data Model. When both tables contain repeated values in the proposed key column, use a separate bridge or lookup table with one distinct row per key, then relate each of the two tables to it. This makes the model’s paths clearer and avoids ambiguous joins.
Excel relationships also do not enforce database-style referential integrity. A sales row can contain a ProductID absent from Products. Such unmatched values will not map to a named product in the expected way, so check for missing keys and blanks when totals appear under a blank category or do not match expectations.
Create a relationship in Excel for Microsoft 365
First, make each source range an Excel table and give it a descriptive name. Select a cell in the range and choose Insert > Table, or use Home > Format as Table. Confirm that the table has headers, then use Table Design > Table Name to set names such as Sales and Products. If importing data, add the tables to the Data Model when the import options offer that choice.
- Load both tables into the workbook’s Data Model. For an existing table, use Power Pivot > Add to Data Model if that command is available.
- Choose Data > Relationships, then select New.
- In the Create Relationship dialog, choose the table on the many side, its key column, the related lookup table, and the matching unique column on that table.
- Confirm the selections and choose OK. The new relationship should appear in the Relationships dialog.
- Create or refresh a PivotTable based on the Data Model, then use fields from both tables to verify the link.
You can also open Power Pivot > Manage and use Diagram View to inspect the model. Dragging a key column to its matching column can create a link, and the Power Pivot window also provides relationship commands. Diagram View is particularly useful once a model has several tables because it shows the tables and their connections together.
If the Power Pivot tab is missing, check File > Options > Add-ins. At the bottom, choose COM Add-ins in the Manage box, select Go, and see whether Microsoft Power Pivot for Excel is available to enable. Availability depends on the Excel edition and installation. Excel for Mac and Excel for the web do not provide the same Power Pivot model-authoring workflow as desktop Excel for Windows.
Plan a model that is easy to understand
A simple star-shaped model is usually a good starting point: one central transaction table connects to lookup tables for products, dates, customers, or locations. For example, Sales can connect separately to Products, Customers, and Calendar. This is easier to reason about than linking every table to every other table.
Use stable keys rather than descriptive labels wherever possible. Product names can change, contain spelling variations, or be shared by different products. A unique ProductID is a better relationship key. Keep the descriptive fields in the lookup table, where a single correction updates how the item appears in reports.
Before linking tables, check that:
- The intended lookup key has no duplicates and identifies one row per entity.
- Both key columns have compatible data types and consistent values, including spaces, leading zeros, and blank entries.
- The lookup table includes every key that should be represented in the data table.
- The relationship direction makes sense: a selection from a lookup table should filter the related transaction rows.
Clear table and column names also matter. Names such as Calendar, Products, and Sales are easier to use in PivotTables and DAX than generic names such as Table1 and Column3. If the model contains multiple dates, label them clearly, such as OrderDate and ShipDate, so report users know which date role they are analyzing.
Active relationships and multiple date columns
A common modeling question arises when a transaction table has more than one date column. An Orders table might contain OrderDate and ShipDate, while a Calendar table has one row per date. Both date columns can be related to Calendar, but only one relationship between those tables can be active at a time. The active relationship is the default path Excel uses in ordinary PivotTable analysis.
For example, if OrderDate is active, Calendar fields will normally group orders by order date. To analyze shipments by ShipDate instead, modelers can use DAX measures that activate the other relationship with the USERELATIONSHIP function. That requires an appropriate measure; simply adding another date link does not make both date paths active simultaneously. For simpler reporting, some models use separate role-playing date tables, such as Order Calendar and Ship Calendar, each connected to its intended date column.
When a model has multiple paths between tables, results can become difficult to interpret. Prefer a clear, deliberate structure and document which relationship is active. If an expected filter does not affect a measure, check the relationship’s active status as well as the measure’s DAX logic.
Relationships versus merging tables
A relationship and a Power Query merge solve related but different problems. A relationship keeps tables separate in the Data Model and connects them for analysis. A merge in Power Query joins columns from one query to another during data preparation, after which you expand the desired columns and load the result.
Use relationships when you want a reusable model with separate transaction and lookup tables, especially when several reports need the same dimensions. Use a merge when you intentionally need a flattened table, need to transform or reconcile source records, or need columns physically included in a query’s output. Merging can increase repetition in a large transaction table, while relationships require a valid model design and appropriate PivotTable or Data Model use.
Neither approach automatically fixes poor keys. If labels do not match or a lookup key is duplicated, a merge can also produce missing or repeated results. Clean and validate the source data before deciding which modeling method fits the report.
Common problems and practical checks
If Excel refuses to create a relationship, start with the lookup-side key. Duplicate values there are a frequent cause. Next, confirm that both tables are in the Data Model, that the selected columns have compatible data types, and that the dialog has the intended table on each side. A worksheet table by itself is not enough if it has not been added to the model.
If a PivotTable shows a blank item, or some records are absent from a category, compare the data-table keys with the lookup keys. Differences can come from extra spaces, inconsistent capitalization in source text, missing lookup records, blank IDs, or values stored as text in one table and numbers in the other. Inspect representative values rather than relying only on how cells are formatted.
If a total seems duplicated or much larger than expected, verify that the lookup key really is unique and that the PivotTable fields come from the intended tables. Also check whether a measure or layout is counting rows when you intended to sum a numeric column. Refresh the model after changing source data by choosing Data > Refresh All, then check that the lookup and transaction tables both reflect the latest source.
Relationships are not visible as worksheet formulas in individual cells. To review or remove them, use Data > Relationships, or inspect Diagram View in Power Pivot > Manage. When sharing a workbook, remember that recipients need a compatible Excel version and access to any external source data if they are expected to refresh the model.
Frequently Asked Questions
Can Excel have relationships between tables on different worksheets?
Yes. The tables can be on different worksheets, but both must be added to the Data Model. Their worksheet locations do not determine the relationship; the selected key columns do.
Does a relationship automatically fill in missing values?
No. It does not copy lookup fields into the data table or repair unmatched keys. Add the missing lookup records or correct the source keys, then refresh the model and review the report.
Can one table have more than one relationship?
Yes. A table can connect to multiple other tables. It can also have more than one relationship to a particular table in some designs, but only one relationship between a given pair of tables can be active at a time.
Why can’t I create a relationship with duplicate IDs?
The lookup side of a one-to-many relationship must have unique values in its relationship column. Remove duplicates or create a separate lookup or bridge table that contains one row for each key.
Can Excel for Mac create Power Pivot relationships?
Excel for Mac does not offer the same Power Pivot data-model authoring features as Excel for Microsoft 365 on Windows. For creating and managing this kind of Power Pivot relationship, use a supported Windows desktop edition of Excel.
Related
- How to Make a Vertical Timeline in PowerPoint Step by Step
- LEGO Inventory Spreadsheet: Track Sets, Parts, and Gaps
- How to Make a Timeline Visual in PowerPoint From Scratch
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table by Month: Group Dates the Right WayTo make an Excel PivotTable by month, put a genuine date field in the Rows area, put the…
- Dashboard from Excel Data: Build a Clear, Useful ViewYes. To make a dashboard from Excel data, organize the source as a clean Excel Table, summarize it…
- Excel Pivot Chart Axis Labels: How to Rotate Them EasilyTo rotate category-axis labels in an Excel PivotChart, select the labels, right-click and choose Format Axis, then open…
- Tree Map Chart in Excel: Create, Read, and Format OneTo create a tree map chart in Excel for Microsoft 365 on Windows, arrange your data in columns…
- Excel Add a Line to a Chart: Steps and OptionsTo add a line to an Excel chart, select the chart, choose Chart Design > Select Data >…
- How to Make a Thermometer Chart in ExcelTo make a thermometer chart in Excel for Microsoft 365 on Windows, enter a completion value and the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.