Excel Data Model relationships connect tables through matching columns, so a PivotTable or report can combine fields without repeating lookup data in every row. In Microsoft 365 for Windows, create a relationship between a unique key in one table and matching values in another, usually linking a lookup table such as Products to a transaction table such as Sales.
What an Excel Data Model relationship is
An Excel Data Model is a collection of tables and their connections, stored in the workbook and powered by the same underlying modeling technology used by Power Pivot. A relationship tells Excel how rows in one table correspond to rows in another. It does not merge the tables or copy columns between them; it lets Excel use their connected fields together when evaluating a PivotTable or a model measure.
For example, a Sales table might contain one row per order line, with OrderDate, ProductID, Quantity, and Revenue. A separate Products table might contain one row per product, with ProductID, ProductName, and Category. A relationship between Products[ProductID] and Sales[ProductID] lets a PivotTable show revenue by product category, even though Category exists only in Products.
Relationships work only between tables included in the Data Model. Two ordinary worksheet ranges do not become related just because they have a column with the same name or values. Excel Tables are a useful starting point: give each table a clear name, include a header row, and make sure the columns intended as keys are consistent.
How relationships work
The common pattern is one-to-many. The table on the one side has a unique value for each key, while the table on the many side can repeat that value. In the example, each ProductID appears once in Products but may appear on many sales rows. Products is the lookup or dimension table; Sales is the fact table that records business events.
When a PivotTable uses Category from Products and Revenue from Sales, Excel follows the relationship to group the sales rows under the appropriate category. A filter on a lookup table, such as selecting one category, can filter the related rows in the fact table. This is why good model design depends on clear keys and a sensible direction from descriptive tables to transaction tables.
Relationships do not automatically resolve every mismatch. A Sales row whose ProductID is absent from Products has no matching product details. Such unmatched keys can be grouped under a blank or unknown item in a report. That is a data-quality signal to investigate, not evidence that Excel guessed the intended product.
Create a relationship in Excel for Windows
For a straightforward setup, first turn each source range into an Excel Table with Ctrl+T, confirm that the headers are unique and meaningful, and add the tables to the Data Model. You can load tables to the model when creating a PivotTable by selecting Insert > PivotTable and choosing the option to add the data to the Data Model, or use Power Pivot to add tables. The exact loading choices can vary with how the data was imported.
- Make sure both tables are available in the Data Model and identify the matching key columns.
- On the ribbon, select Data > Relationships, then select New.
- Choose the table on the many side and its key column, then choose the related lookup table and its unique key column.
- Confirm the selections and create the relationship. Use Insert > PivotTable to build a report from the workbook’s Data Model.
For example, select Sales and ProductID as the table and column on the many side, then Products and ProductID as the related table and column on the one side. In the PivotTable Fields pane, place Products[Category] in Rows and Sales[Revenue] in Values. Excel can then summarize revenue by category through the relationship.
If the Data > Relationships command is unavailable or the tables are not listed, check that they have been added to the model. Power Pivot can also show and manage model tables in Diagram View. Power Pivot availability depends on the Excel edition and installation; if needed, check File > Options > Add-ins and the COM Add-ins list for Microsoft Power Pivot for Excel.
Choose and prepare the right key columns
A key is the column Excel uses to match records. A product code or customer ID is usually safer than a name: names can be duplicated, changed, or spelled differently. The column on the one side must uniquely identify each row in that lookup table. If the same ProductID occurs twice in Products, Excel cannot treat that column as a valid unique side.
Before creating a relationship, check these practical details:
- Uniqueness: Remove duplicates from the lookup key or determine why they exist. A product table with multiple rows per product may need a different design.
- Consistent data types: The two related columns should use compatible, matching data types. A number stored as text is not reliably equivalent to the same value stored as a number.
- Consistent values: Watch for leading or trailing spaces, inconsistent codes, and missing identifiers. Standardize values in the source or with Power Query before loading the model.
- One key column: Excel relationships connect a single column on each side; they do not create a relationship from a pair of columns together. If a real-world key is composite, create a stable combined key upstream or in the query process.
Blank or unmatched identifiers deserve attention, too. A blank should not be used as a substitute for several different unknown products or customers. If a transaction has no known match, a deliberate Unknown member in the lookup table can make reporting more understandable, provided the data is modeled consistently.
Designing a useful model
For reporting, a simple star-shaped design is often easier to understand than a web of interconnected transaction tables. Put measurable events—such as sales, shipments, or support cases—in a fact table. Put descriptive attributes—such as product category, customer region, or calendar year—in separate dimension tables. Give each dimension a unique key and connect it to the matching key in the fact table.
This design helps avoid repeated descriptions and makes PivotTable fields easier to interpret. A Sales table can store ProductID rather than copying product name, category, and supplier into every order line. The Products table stores those descriptions once. If a product’s category is corrected in the lookup table, reports can use the corrected description without editing every transaction row.
Date data merits particular care. A calendar table should generally have one row per date and a unique date column, related to the date column in the fact table. A dedicated calendar table can provide consistent year, quarter, month, and other date attributes across reports. For time-intelligence calculations, the date table’s coverage and continuity matter; ensure its dates include the period needed by the model.
Relationships, filters, and PivotTables
Once connected, tables contribute fields to the same PivotTable. You can put a category from a lookup table in Rows and a numeric field from a fact table in Values, or use a date attribute to group transactions. The relationship supplies the path between the fields; it does not decide what the numeric field means. For example, summing Revenue is different from counting sales rows, and the chosen aggregation still matters.
Measures and calculated fields can also work with related tables in the Data Model. Measures use DAX and are evaluated in the report’s filter context, so selecting a year or category changes the records included in a result. This is more flexible than writing a separate worksheet formula for each report layout, but it requires learning DAX concepts if the built-in PivotTable summaries are not enough.
Excel’s model relationships are not a general-purpose many-to-many join that automatically handles every arrangement. The usual relationship requires a unique lookup side and repeated values on the fact side. When a business situation genuinely involves multiple matches on both sides, it may require a bridge table or a revised model design rather than simply connecting the two tables directly.
Common relationship problems
A relationship that cannot be created usually points to a setup issue: one table is not in the model, the selected lookup column contains duplicates, or the two columns have incompatible types. Check the actual values as well as the displayed formatting. Formatting a text ID to look numeric does not necessarily convert its underlying data type.
A relationship that exists but produces blanks or unexpected totals often indicates unmatched keys, duplicate records, or an incorrect relationship path. For example, if a product table contains duplicate ProductID values, it is not a clean product lookup table. If a transaction uses an ID absent from the lookup, its descriptive fields cannot be assigned to the intended product. Review distinct keys and sample the records rather than relying on the visual appearance of a PivotTable alone.
Multiple relationships between the same tables can also cause confusion. Excel can have an active relationship and, in some model scenarios, an inactive alternative; only the active relationship is used by default for normal filtering. DAX can explicitly use an inactive relationship in a measure with the USERELATIONSHIP function. If a report behaves differently than expected, inspect the relationship diagram and the measure definition.
Another common misconception is that matching column names create relationships automatically. They do not. Nor does a relationship physically combine tables as a Power Query merge would. Relationships preserve the separate tables and connect them for model analysis; a merge instead creates a query result with columns brought together. Choose based on whether you want a reusable relational model or a combined output table.
Excel Data Model versus formulas and Power Query
A lookup formula such as XLOOKUP can bring a value from another table into a worksheet row. That is useful for a compact worksheet calculation or when each row needs a displayed value. A Data Model relationship is more suitable when a PivotTable should analyze fields from multiple tables without duplicating lookup columns throughout the fact data.
Power Query serves a different purpose: it imports, cleans, reshapes, appends, and merges data before it is loaded. A typical workflow is to use Power Query to standardize ProductID values and remove unwanted rows, load the cleaned Products and Sales tables to the Data Model, and then relate them for reporting. Power Query can merge tables, but that does not replace the need to understand relationships if the final model keeps tables separate.
Excel for Mac and Excel for the web do not provide the same Power Pivot and Data Model relationship-authoring experience as Excel for Microsoft 365 on Windows. A workbook created with a model may have limitations when opened elsewhere, so use supported Windows desktop Excel to create or manage these relationships and verify compatibility before sharing model-dependent reports.
Frequently Asked Questions
Do Excel Data Model relationships update automatically?
Relationships remain part of the workbook model, but changes to the source data may require a refresh, depending on how the tables were loaded. Use Data > Refresh All to refresh supported connections and queries, then check the PivotTable results.
Can I relate two tables if the key repeats in both?
Not as a normal one-to-many relationship using those repeated columns on both sides. The lookup side must be unique. A bridge table or a redesigned set of dimensions may be needed for a genuine many-to-many situation.
Do related tables have to be on the same worksheet?
No. The tables can be on different worksheets or can come from external sources, as long as both are included in the Data Model and the relationship columns are suitable.
Why does my PivotTable show a blank category?
A fact-table key may be blank or may not match any key in the related lookup table. Check for missing IDs, inconsistent types, extra spaces, and lookup records that have not been loaded.
Can I create Excel Data Model relationships on a Mac?
Excel for Mac does not provide the Windows Power Pivot relationship-authoring tools described here. For creating and managing the model, use a supported desktop version of Excel for Windows.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Lock Cells in Excel for the Web and Protect DataTo lock cells in Excel for the web, unlock any cells people should still be able to edit,…
- Google Sheets COUNTIF: Count Non-Empty Cells in a RangeIf your entries are in A2:A100, count cells that are not empty in Google Sheets with =COUNTIF(A2:A100,"<>"). The…
- Google Sheets Count If Not Blank: Formulas and FixesTo count cells that are not blank in Google Sheets, use =COUNTA(A2:A100), replacing the range with your own.…
- How to Compare Two Lists in Google Sheets Step by StepTo compare two lists in Google Sheets, put the first list in column A and the second in…
- How to Add a Yes or No Checkbox in Microsoft ExcelIn Microsoft Excel for Microsoft 365 on Windows, select the cell or range where you want a checkbox,…
- Excel Conditional Formatting for Absolute ValuesTo conditionally format values by absolute value in Excel, select the cells, choose Home > Conditional Formatting >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.