How to join tables with Excel's Data Model
By the Analistable team · Updated · 5 min read
Format each table with Ctrl+T, add them to the Data Model (Insert → PivotTable → tick Add this data to the Data Model, or load from Power Query), then create relationships in Data → Relationships: for example Orders[Customer ID] → Customers[Customer ID]. A PivotTable can then use fields from both tables without a single lookup formula. The Data Model is available in Excel for Windows only.
Part of our guide: How to join spreadsheets on a common column
Why use relationships instead of lookups
- No helper columns: you don't copy Customer Name into every order row.
- Handles millions of rows, because the Data Model compresses data in memory rather than on the sheet.
- One customer table serves every fact table (orders, returns, tickets).
- Totals stay correct: each measure is calculated from its own table.
Step by step
- Format Orders, Customers and Products as tables and name them (Table Design → Table Name).
- Click in Orders, choose Insert → PivotTable, and tick Add this data to the Data Model.
- Add the other tables to the model: select each and choose Data → From Table/Range, then in Power Query Close & Load To → Only Create Connection + Add this data to the Data Model.
- Choose Data → Relationships → New: Table Orders, Column Customer ID; Related Table Customers, Related Column Customer ID. Repeat for Products.
- In the PivotTable Fields pane, click All and drag fields from any table.
| Customers[Country] | Desk | Chair | Lamp |
|---|---|---|---|
| Denmark | 75 | ||
| France | 120 | 80 |
Requirements and common errors
| Rule | If broken |
|---|---|
| The related (lookup) column must have unique values | Excel refuses to create the relationship |
| Both columns must have the same data type | No matches; blank rows in the pivot |
| Use fields from the lookup table for rows and columns | Same total repeated on every row |
| One active path between two tables | Excel marks the extra relationship inactive |
Measures with DAX
With the Power Pivot add-in enabled (File → Options → Add-ins → COM Add-ins), you can add measures such as Revenue := SUM(Orders[Amount]) or Avg order := DIVIDE([Revenue], COUNTROWS(Orders)), and calculated columns such as =RELATED(Customers[Country]) on the Orders table.
Building a pivot from several sheets that share columns is a different task — see creating a pivot table from multiple sheets. For one-off lookups, XLOOKUP is simpler.
Worked example: three tables, one PivotTable
Orders has four rows (amount 120, 75, 80 and 60 for products Desk, Chair, Lamp and Desk), Customers has country per customer, and Products has category per product. The fourth order belongs to customer C-104, who isn't in Customers.
| Country | Amount |
|---|---|
| Denmark | 75 |
| France | 200 |
| (blank) | 60 |
| Grand Total | 335 |
75 + 200 + 60 = 335, the total of the Orders table. The (blank) row is the order whose customer has no match; the Data Model keeps it rather than dropping it, so the grand total stays honest. Filter it out only after you've decided what to do with unknown customers.
Relationships or Power Query merge?
| Need | Better choice |
|---|---|
| PivotTables slicing one fact table by several lookup tables | Data Model relationships |
| A flat table on a sheet to filter, export or send | Power Query Merge |
| More than about a million rows | Data Model (a sheet stops at 1,048,576) |
| Formulas on the sheet that use the looked-up value | XLOOKUP or a merge |
| Excel for Mac or Excel for the web users editing the model | Merge, since the model can't be edited there |
Merging in Power Query is covered in Power Query join kinds; the same idea in Power BI is in merging tables in Power BI.
Troubleshooting relationships
| Symptom | Cause | Fix |
|---|---|---|
| “Relationships between tables may be needed” banner | Fields come from tables with no relationship | Click Create or Data → Relationships → New |
| Error: the related column contains duplicate values | The lookup table repeats a key | Remove duplicates in the lookup table, ideally in Power Query |
| Large (blank) row in the PivotTable | Fact keys with no match, often spaces or different types | Trim keys and set both to the same data type |
| Relationship appears greyed out (inactive) | There is already a path between the two tables | Remove the redundant relationship, or use USERELATIONSHIP in a DAX measure |
Keeping the model up to date
If the tables are loaded through Power Query, Data → Refresh All reloads them and the relationships stay in place. Add new monthly data by appending it in Power Query rather than creating new tables, so the PivotTables and relationships don't need rebuilding. To check the model after each refresh, compare the PivotTable grand total with a simple =SUM() of the source column.
Excel versions and platforms
| Version | Create relationships and model PivotTables |
|---|---|
| Microsoft 365, Excel 2021, 2019, 2016 for Windows | Yes (Data → Relationships; Power Pivot for DAX) |
| Excel for Mac | No Data Model or Power Pivot |
| Excel for the web | Can open and use PivotTables from an existing model; limited or no model editing |
If colleagues use Macs, build the merged table in Power Query or with XLOOKUP so the workbook works for everyone, and keep the Data Model for Windows-only analysis files.
Frequently asked questions
- How do I join tables in Excel without VLOOKUP?
- Add the tables to the Data Model and create relationships in Data → Relationships, then build a PivotTable from the Data Model.
- Is the Data Model available on Mac?
- No. Excel for Mac doesn't support the Data Model or Power Pivot.
- Why does my pivot show the same value on every row?
- The row field comes from a table that isn't related to the value's table, or the relationship runs the wrong way. Use fields from the lookup table and check the relationship.
- Can a Data Model relationship use two columns?
- No. A relationship links one column to one column. Create a combined key column in both tables, such as Store & "|" & SKU, and relate those.
- Do I need Power Pivot to use the Data Model?
- No. Data → Relationships and PivotTables from the Data Model work without it. Power Pivot adds the model diagram view and DAX measures.
- Why can't Excel create my relationship?
- Both columns contain duplicate values, or their data types differ. The lookup table's column must be unique, and both columns should share a type.