Excel Data Table Relationships and How to Create Them

How-to basics · Updated November 19, 2026

In Excel, table relationships link separate tables through matching key columns, letting PivotTables and the Data Model analyze them together without merging their rows. Create a relationship from Data > Relationships after adding both tables to the Data Model; the key on the lookup side must contain unique values.

What Excel table relationships mean

A relationship is a link between two tables based on columns that contain corresponding values. One table commonly holds descriptive details, such as product names or customer regions, while another holds repeated transactions, such as orders or sales. The related columns do not have to share a name, but their values must match in a useful way.

Excel stores these links in its Data Model, the workbook’s internal structure for combining tables. A relationship does not physically merge tables, copy values between them, or change the source rows. Instead, it tells Excel how to connect the tables when a PivotTable or other Data Model report uses fields from both.

This is different from formatting a range as an Excel table with Insert > Table. A formatted table has a name, filter buttons, and structured references, but that alone does not create a relationship. It is also different from a What-If Analysis data table, which calculates results for different input values. The shared word “table” can make these features easy to confuse.

A simple example: orders and products

Imagine an Orders table with OrderID, ProductID, Quantity, and OrderDate columns. Each order row records a transaction, so the same ProductID may appear many times. A separate Products table has one row per product, with ProductID, ProductName, and Category. ProductID is the common key: it appears repeatedly in Orders but only once in Products.

After linking Orders[ProductID] to Products[ProductID], a PivotTable can summarize quantities by ProductName or Category while drawing transaction values from Orders. Without a relationship, you might copy product names into every order row or build a more complicated lookup formula. Keeping product details in one table avoids repeated descriptions and makes updates easier: change a product name once in Products rather than in hundreds of order records.

The same pattern works for customers and sales, employees and assignments, or dates and transactions. The table with unique descriptive records is often called the lookup, dimension, or “one” table. The table with repeated events is the fact, transaction, or “many” table.

Prepare tables before creating a relationship

Good relationships depend on clean, compatible keys. Before connecting tables, check that the lookup table has exactly one row for each key and that the corresponding columns use compatible data types. A number stored as text is not necessarily treated like the same value stored as a number. Leading zeros also matter: customer code 0042 may not be interchangeable with numeric value 42.

  • Give each source range a clear header row, with no blank or duplicate column names.
  • Remove duplicate key values from the lookup table, or decide which record should represent each key.
  • Check for extra spaces, inconsistent capitalization, missing codes, and values present in one table but not the other.
  • Convert each source range to an Excel table with Insert > Table, and assign a recognizable name using Table Design > Table Name.

A relationship is not a way to clean mismatched data. For example, if one table uses “North” and another uses “NORTH ” with a trailing space, those values may not match as intended. Clean the source data first, or use Power Query to standardize it. Also check what unmatched records mean for your report: an order with a ProductID missing from Products may not appear under a named product category in a PivotTable.

Add tables to the Data Model

Excel relationships are Data Model relationships, so the tables must be in the model before you can connect them. In Microsoft 365 for Windows, select a cell in the first table and choose Insert > PivotTable. In the Create PivotTable dialog, select the option to add the data to the Data Model. Repeat for the other table if necessary. Depending on how you create the PivotTable, the dialog may also offer the workbook’s existing Data Model as its source.

If the Power Pivot tab is available, you can also select a table and use Power Pivot > Add to Data Model. The Power Pivot add-in may need to be enabled if that tab is not displayed. Avoid adding an ordinary worksheet range directly when a formatted Excel table is available; tables expand more naturally as new rows are added.

On Excel for Mac, Data Model and Power Pivot authoring features are not equivalent to Excel for Windows, and some relationship workflows may be unavailable or limited depending on the build. If you do not see the Data Model or Relationships commands, check the capabilities of your Excel version rather than assuming a worksheet table itself supports relationships. Excel for the web also has a more limited Data Model authoring experience; Windows desktop Excel is the most dependable place to create and manage these relationships.

Create a relationship in Excel

Once both tables are in the Data Model, create the link in Excel for Microsoft 365 on Windows:

  1. Choose Data > Relationships.
  2. In the Manage Relationships window, select New.
  3. In Table, choose the table on the many side, such as Orders. In Column (Foreign), choose its repeated key, such as ProductID.
  4. In Related Table, choose the lookup table, such as Products. In Related Column (Primary), choose the unique key, also ProductID.
  5. Select OK, then close the relationship manager.

Excel will reject a relationship if the proposed primary column contains duplicate values or the columns are not compatible. The relationship does not require the two columns to have identical names, but it does require a valid match between their values. You can return to Data > Relationships later to review or manage links.

How relationships affect PivotTables

After a relationship exists, create a PivotTable using the workbook’s Data Model as its source. In the PivotTable Fields list, fields from multiple related tables can be used together. For example, place Products[Category] in Rows and Orders[Quantity] in Values. Excel uses the relationship to associate each order’s ProductID with the correct product category, then aggregates quantities.

In a typical one-to-many relationship, filtering a lookup-table field limits the related transaction rows. Selecting one category, for instance, filters the products in that category and the orders for those products. Relationships let you build reports from normalized tables, where repeated descriptions are stored once, instead of requiring a single flattened source table.

Relationships do not automatically make every worksheet formula aware of every table. They are primarily used by the Data Model and reports built from it. A regular formula that refers to a cell or table column still follows normal formula rules; it does not gain a lookup result just because a relationship exists.

Choose a relationship design that fits the data

The most common useful structure is one-to-many: one row per product in Products and many order rows in Orders. The unique key belongs on the lookup side, and the repeated foreign key belongs on the transaction side. A date table can play the same lookup role when it has one row per date and a transaction table has many events on each date.

Excel relationships rely on a single column as the key; they do not directly define a relationship using a pair of columns as a composite key. If a record is only unique when two values are combined, create a reliable single key in both tables or prepare the data so there is a unique identifier. Take care when combining text values: simple concatenation can create collisions if the components are not separated or encoded consistently.

Many-to-many situations need extra thought. For instance, if products can belong to several promotions and each promotion covers several products, neither table may have a unique key that directly supports the intended link. A bridge table with one row per ProductID–PromotionID pairing can represent those memberships as two one-to-many relationships. Build and validate that structure before interpreting totals, because duplicate paths or repeated memberships can otherwise produce confusing summaries.

Relationships versus Power Query merges

A Data Model relationship and a Power Query merge solve related but different problems. A relationship keeps tables separate and connects them for Data Model analysis. A merge combines columns from two queries into a resulting query table, which can be useful when you need a flattened output or want to shape data before loading it.

Choose relationships when you want separate lookup and transaction tables for PivotTables and Data Model reporting, especially when the same lookup table serves several fact tables. Choose a Power Query merge when you specifically need to join columns into one prepared table, or when you need data-cleaning steps as part of the import process. You can also use both: clean or reshape sources in Power Query, load the resulting tables to the Data Model, then create relationships.

Common relationship problems

  • Duplicate values on the lookup side: The column on the one side must be unique. Remove duplicates or build a proper lookup table before connecting it.
  • Different data types: Standardize both key columns as the same appropriate type. Do not convert identifiers with leading zeros to numbers if those zeros are meaningful.
  • Unmatched keys: Check for missing lookup records, spelling differences, trailing spaces, and inconsistent codes. Decide whether unmatched transactions should be corrected or reported separately.
  • Relationship command unavailable: Confirm both tables are in the Data Model and that you are using a version with the needed Data Model tools.
  • Unexpected totals: Check whether the PivotTable uses fields from the intended tables and whether duplicate or many-to-many records cause repeated counts. A relationship connects rows; it does not guarantee that every measure is being aggregated appropriately.

When a report is wrong, inspect a small set of known records first. Follow a transaction’s key into the lookup table and confirm that it reaches one intended row. This simple check often reveals a duplicate, an unmatched key, or a data-type inconsistency faster than changing the PivotTable layout at random.

Frequently Asked Questions

Do Excel tables automatically create relationships?
No. Insert > Table formats a range as an Excel table, but you must add the tables to the Data Model and create the relationship separately.

Can I relate tables that have different column names?
Yes. The key columns can have different names, as long as their values correspond and their data types are compatible.

Why does Excel say the relationship cannot be created?
Common causes include duplicate values in the lookup-side key, incompatible data types, or tables that are not both in the Data Model. Check those conditions first.

Do relationships merge data into one worksheet table?
No. They connect separate Data Model tables for analysis. Use Power Query’s merge operation if you need to combine columns into a new query result.

Can a regular Excel formula use a Data Model relationship?
Not automatically. A relationship supports Data Model analysis, such as PivotTables built from the model; worksheet formulas still need their own references or lookup logic.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.