A pivot table can summarize data from two separate tables at the same time — even when those tables live in different sheets, even when they were never merged, and even when they share no formulas at all. No VLOOKUP. No helper column. No copy-paste job that has to be redone every month.
That last point is the one that surprises most people. The common assumption is that analyzing two tables together requires physically combining them into one flat range first. The Data Model exists specifically to avoid that step, and once a relationship is in place, the connection is remembered by the workbook and survives every refresh.
Myth vs. reality: what the Data Model is not
Before walking through the steps, it’s worth clearing up a handful of assumptions that cause people to avoid this feature entirely.
Myth: You need Power Pivot, a separate add-in, or a paid version of Excel. Reality: The Data Model ships inside ordinary Excel for Windows and Excel for Mac. It’s been there since Excel 2013, and it’s enabled by default. You don’t need to install anything; you don’t need to visit the Developer tab. The only visible difference between a normal pivot table and a Data Model pivot table is a checkbox most people have never clicked.
Myth: Relationships require matching column names or matching number of rows. Reality: Relationships care about matching values, not matching labels. A column named “CustomerID” on one table can relate to a column named “Cust_No” on the other, provided both columns contain the same kind of identifier and the same underlying values. Row counts can differ completely — that’s the point of keeping them separate.
Myth: You have to combine the tables into one sheet first. Reality: Combining is exactly the behavior the Data Model replaces. If you’re still pulling customer names into an order table with VLOOKUP, you’re building a relationship manually and paying for it with a helper column that breaks whenever a new customer appears.
Myth: Relationship columns need to be sorted or adjacent. Reality: Sorting and column position are irrelevant. Excel stores each column’s distinct values in memory and matches them by value, not by position.
Myth: Relationships are read-only and fragile. Reality: A working relationship persists across refreshes. New rows added to either source table are matched automatically on refresh, as long as the new values follow the same pattern.
The setup this guide assumes
To keep the steps concrete, imagine a workbook with two tables on two different sheets.
- Orders — one row per order. Columns: OrderID, CustomerID, OrderDate, Amount. Roughly 5,000 rows and growing every week.
- Customers — one row per customer. Columns: CustomerID, CustomerName, Region, SignupDate. Roughly 400 rows and updated occasionally.
The goal is a single pivot table that shows Amount by Region, pulling Region from Customers and Amount from Orders. That combination is impossible with a normal pivot table because Region doesn’t exist in the Orders table and Amount doesn’t exist in the Customers table. A Data Model relationship makes it possible.
One naming note straight away: the columns that will be linked (here, CustomerID on both tables) don’t need identical names. But making them easy to recognize saves you from sorting through a dropdown of every column in the workbook later.
Before you start: three things that will save you time
- Convert both ranges to actual Excel Tables first. Select each range and press Ctrl+T (Cmd+T on Mac). A Table has a defined name and grows automatically as rows are added, which the Data Model prefers. An ordinary range works too, but a Table is more reliable for ongoing use.
- Confirm each table has a unique key column. One table — the “many” side — can have repeated values in its key column (many orders per customer). The other table — the “one” side — must have uniquely identifying values (one row per customer). If the “one” side has duplicate CustomerIDs, the relationship is either invalid or produces total counts that are silently wrong.
- Clean the key column on both sides before linking. Leading spaces, mixed text-and-number formatting, and trailing non-breaking spaces are the most common reasons a relationship “doesn’t match anything.” A quick pass with TRIM, or a Text to Columns run on the key column, resolves most of these in under a minute.
Step 1: Load the first table into the Data Model
Click any cell inside the Orders table, go to the Insert tab, and choose PivotTable. In the dialog that opens, Excel will already have the table range filled in. Before you click OK, look for the checkbox at the bottom of the dialog:
Add this data to the Data Model
Tick it. Choose where you want the pivot table to go, then click OK. The pivot table shows up on the sheet, but the Field List looks slightly different from a normal one — the source tables appear as separate tables at the top, not as one merged list of columns. That’s your first signal the Data Model is active.
Step 2: Load the second table into the same Data Model
The trick at this stage is that Excel won’t offer the “Add to Data Model” checkbox when you insert a second pivot table from a new source the usual way. Instead, the standard approach is:
- Click any cell in the Customers table.
- Go to the Data tab and choose From Table/Range in the Get & Transform group.
- In the Power Query Editor that opens, do nothing — just click Close & Load To… in the top-left.
- In the dialog, choose Only Create Connection and tick Add this data to the Data Model. Click OK.
The Customers table now lives in the Data Model alongside Orders. Nothing appears on any sheet, which is exactly what should happen — the connection is behind the scenes.
Step 3: Create the relationship itself
This is the step that most tutorials overcomplicate. Two routes work, and they produce identical results.
Route A (no extra windows needed): With the pivot table’s Field List visible, click All at the top to show every table in the workbook. Drag a field from the Customers table — say, Region — into the Rows area. Excel notices that Region’s table isn’t yet linked to the pivot table’s source and asks whether you want to create a relationship. Click Create, and the relationship dialog opens automatically. Pick CustomerID from both sides, confirm the correct tables are shown, and click OK.
Route B (explicit): Go to the Data tab, click Relationships in the Data Tools group, then New. Choose the Customers table as the related (lookup) table and the Orders table (or the pivot table’s source) as the main table. Select CustomerID from each dropdown. Click OK.
Whichever route you take, the setup looks like this:
- Table: Orders (the “many” side, where CustomerID repeats)
- Related Table: Customers (the “one” side, where CustomerID is unique)
- Column: CustomerID
- Related Column: CustomerID
A single-side arrows icon appears once the relationship is created. That arrow is the visual cue that the relationship is live.
Step 4: Test it with a deliberately wrong-looking pivot table
Don’t jump straight to the finished report. Build something simple that only exists because the relationship works.
- Rows area: Region (from Customers)
- Values area: Sum of Amount (from Orders)
If Region shows four or five categories and the Amount column shows nonzero totals for each, the relationship is functioning. If Region appears but every Amount cell shows blank, the relationship exists but is pointing at columns that don’t match values — go back to Step 3 and confirm the key columns are both pointing at the identifier, not the name.
What to check if it’s not working
Few Data Model problems are mysterious once you know where to look. The symptoms below cover the vast majority of first-attempt failures.
| What you see | What it usually means | What to do |
|---|---|---|
| Amount shows blank for every region | Key columns contain different value types (numbers vs. text) | Convert the key column on both sides to the same type, refresh |
| A few regions show blank totals | Some CustomerID values don’t exist in the Customers table | Find the orphan orders and either add the missing customer or exclude those orders |
| “The relationship may be invalid” warning | The “one” side has duplicate values in its key column | Deduplicate the Customers table, or accept a many-to-many relationship only if the logic supports it |
| Relationship can’t be created at all | The two columns were loaded from different data sources under incompatible types | Re-load the source with matching data types |
| Region field doesn’t appear in the Field List | The Customers table was loaded as a connection but not added to the Data Model | Redo Step 2 and confirm the “Add this data to the Data Model” box is ticked |
A common recovery move: if you suspect the key columns, temporarily drag CustomerID from both tables into a plain pivot table built off each source separately, and compare the lists. Sorting both alphabetically and glancing for mismatches usually reveals the culprit within seconds.
Once the relationship works, don’t rebuild it every month
Two habits keep a working Data Model intact over time.
First, right-click the pivot table and set Refresh data when opening the file under PivotTable Options → Data. That way, new orders added to the Orders table are picked up automatically, and the relationship re-evaluates without any manual intervention.
Second, resist the temptation to paste new values on top of the key column. If you receive an updated customer list, replace the contents of the Customers table rather than pasting into only part of the column — partial replacement is the most common cause of mixed data types on the key column.
What to do next
The natural next step is a second relationship, this time linking Orders to a Products or Dates table, and building a pivot with fields pulled from three tables at once. The mechanics are identical — one table on the “one” side, the other on the “many” side, a matching key, and a pivot table built off the Data Model.
If it helps, keep Step 4’s minimal test pivot (Region by Sum of Amount) saved on its own sheet as your diagnostic view. Whenever a new relationship misbehaves, build that same two-field pivot off the tables involved — it isolates whether the problem is in the relationship itself or somewhere further downstream in the report.
And when a relationship goes wrong, check the key columns on both sides first. In practice, that resolves most “it’s not matching” cases faster than any other single check.