Pivot Table from Multiple Sheets: Data Model Relationships

Part of the free Module 10: Pivot Tables · Lesson 18 of 20 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 (Data Model requires Excel 2013 or later on Windows).

A pivot table from multiple sheets lets you summarise two or more tables in one report without copying columns across with VLOOKUP. You add each table to the Excel Data Model, create a relationship on a shared key column such as Employee ID, and then drag fields from both tables into the same pivot table.

What you need before you start

Check these four points first:

  • Each range is an Excel Table. Click inside the data and press Ctrl+T. Then give it a clear name in Table Design > Table Name, for example tblSales and tblEmployees. Names make the Fields pane readable.
  • A common key column. Both tables need a column with the same meaning, such as Employee ID. On the lookup side (the Employees table) every ID must appear exactly once.
  • Consistent data types. If the ID is a number in one table and text in the other, the relationship will not match rows. Convert one side so both are the same.
  • No blank header cells and no merged cells. Every column needs a unique heading.

Power Pivot is not required for this. The Data Model engine ships with every Windows version of Excel since 2013, and relationships are created from the Data tab. Excel for Mac does not support the Data Model, so Mac users should use the Power Query method below.

Method 1: Add tables to the Data Model and create a relationship

  1. Click inside the first table (for example tblSales) and choose Insert > Tables > PivotTable > From Table/Range.
  2. In the dialog tick Add this data to the Data Model, choose New Worksheet and click OK.
  3. In the PivotTable Fields pane click All at the top. Every table in the workbook is now listed, not only the one you started from.
  4. Expand tblSales and drag Revenue to Values. Expand tblEmployees and drag Region to Rows.
  5. A yellow bar appears in the Fields pane: Relationships between tables may be needed. Click Auto-Detect. Excel looks for columns with the same name and matching values and creates the relationship for you.
  6. If Auto-Detect finds nothing, click Create instead and fill in the four boxes described in the table below. Click OK.

The pivot table now shows the correct revenue per region, even though Region lives in a different table from Revenue. You can manage the link at any time from Data > Data Tools > Relationships.

Create Relationship box What to choose Example
Table The table with many rows per key (the fact or data table) tblSales
Column (Foreign) The key column in that table; duplicates are allowed Employee ID
Related Table The lookup table with one row per key tblEmployees
Related Column (Primary) The key column in the lookup table; every value must be unique Employee ID

The direction matters. The many side (Sales) points to the one side (Employees). If you reverse them, Excel reports duplicate values and refuses to create the relationship.

Method 2: Append sheets with Power Query

Relationships join tables with different columns. When your sheets have the same columns, for example one sheet per month, you do not want a relationship; you want to stack them into one long table. Power Query does this in a few clicks.

  1. Convert each monthly sheet to a table with Ctrl+T and name them tblJan, tblFeb and so on.
  2. Click inside the first table and choose Data > Get & Transform Data > From Table/Range. In the Power Query Editor choose Home > Close & Load To, select Only Create Connection and click OK. Repeat for each table.
  3. Choose Data > Get Data > Combine Queries > Append. Pick Three or more tables, add every monthly query and click OK.
  4. Choose Home > Close & Load To. Select PivotTable Report, or tick Add this data to the Data Model if you also want relationships to other tables.

When a new month arrives, add its query to the Append step and press Data > Refresh All. Power Query is available in Excel 2016 and later (and as a free add-in for 2010 and 2013). Excel for Mac has supported From Table/Range and Append since 2023.

Method 3: Multiple Consolidation Ranges (legacy)

Excel still contains the old PivotTable Wizard. Press Alt+D, release, then press P. Choose Multiple consolidation ranges, add each sheet range and finish. Excel builds a pivot table with only four fields: Row, Column, Value and Page. Every numeric column is flattened into a single Value field, so you lose the ability to analyse by product, region or any other dimension. Use it only for a quick total across identically laid-out summary sheets. For real analysis, choose Method 1 or Method 2.

Which method should you use?

Method Best for Excel version Limits
Data Model relationships Tables with different columns that share a key (sales plus employees, orders plus products) 2013 and later, Windows only No calculated fields or items; one-to-many only
Power Query Append Sheets with the same layout (monthly, regional, yearly files) 2016 and later; Mac 2023 and later Needs a refresh after edits; columns must match by name
Multiple Consolidation Ranges A fast total across small summary sheets All versions Only one Value field; no real field analysis

Worked example: sales and employees in one pivot table

Suppose the practice workbook has two tables on two sheets.

tblSales on the Sales sheet:

Date Employee ID Revenue
03-Jan-2026 E101 4,200
05-Jan-2026 E102 3,100
09-Jan-2026 E103 5,650
12-Jan-2026 E101 2,900
15-Jan-2026 E104 6,300
18-Jan-2026 E102 1,750

tblEmployees on the Employees sheet:

Employee ID Name Region
E101 Aarav North
E102 Bhavna South
E103 Chirag North
E104 Divya West
  1. Click inside tblSales, choose Insert > PivotTable > From Table/Range, tick Add this data to the Data Model and click OK.
  2. Click All in the Fields pane. Drag Revenue from tblSales to Values and Region from tblEmployees to Rows.
  3. Click Auto-Detect on the yellow bar. Excel reports one relationship created: tblSales[Employee ID] to tblEmployees[Employee ID].

Result:

Region Sum of Revenue
North 12,750
South 4,850
West 6,300
Grand Total 23,900

North is 4,200 + 2,900 (Aarav) plus 5,650 (Chirag). Adding Name under Region gives a per-person breakdown instantly.

Data Model limitations you should know

A Data Model pivot table looks like a normal one but behaves differently in a few places:

  • No calculated fields or calculated items. The PivotTable Analyze > Calculations > Fields, Items & Sets menu is greyed out for Data Model pivots. Instead you write a measure in DAX: right-click the table name in the Fields pane and choose Add Measure (Excel 2016 and later), or use Power Pivot > Measures > New Measure after enabling the Power Pivot tab under File > Options > Add-ins > COM Add-ins.
  • Measures are more powerful than calculated fields. A simple example that behaves like Sum of Revenue but can be reused in any pivot table:
Total Revenue := SUM ( tblSales[Revenue] )
Average Sale := AVERAGE ( tblSales[Revenue] )
Revenue per Employee := DIVIDE ( [Total Revenue], DISTINCTCOUNT ( tblSales[Employee ID] ) )
  • Some summary functions are missing. Product, Var and Varp are not offered in Value Field Settings; write a measure instead.
  • Grouping dates works in Excel 2016 and later. In Excel 2013 you must add Year and Month columns to the source table.
  • One-to-many only. Excel cannot create a many-to-many relationship. If both tables repeat the key, build a small bridge table of unique keys and relate both tables to it.

Errors and how to fix them

Message or symptom Cause Fix
Relationships between tables may be needed Fields from two tables are in the pivot but no link exists Click Auto-Detect or Create and map the key columns
The relationship cannot be created because each column contains duplicate values The Related (Primary) column is not unique, or the tables are the wrong way round Swap Table and Related Table, or remove duplicate IDs from the lookup table with Data > Remove Duplicates
Every region shows the same grand total Relationship missing or key types do not match (text vs number) Open Data > Relationships; convert both key columns to the same type and refresh
A (blank) row appears in the pivot Some sales have an Employee ID that is not in tblEmployees Add the missing IDs to the lookup table, or filter (blank) out
Many-to-many relationship not supported Both tables repeat the key Create a bridge table of unique keys and relate both tables to it
We couldn’t get data from the Data Model Corrupt model, or a table was deleted while the model still references it Refresh All; if it persists, remove the table from the model in Power Pivot > Manage and add it again, or rebuild in a new workbook
Add this data to the Data Model is greyed out Excel for Mac, or a 32-bit build with add-ins disabled Use the Power Query Append method, or move to Excel for Windows

Tips and common mistakes

  • Name your tables before you start. Table1 and Table2 in the Fields pane cause wrong drags; tblSales and tblEmployees do not.
  • Put the lookup table in Related Table. The most common relationship error is simply having the two tables in the wrong boxes.
  • Trim your keys. A trailing space in “E101 ” breaks the match. Use =TRIM(A2) in a helper column or clean the data in Power Query.
  • Refresh after adding rows. New rows in an Excel Table are picked up automatically, but the pivot still needs Refresh All.
  • Do not add the same table twice. A range added to the model as both a table and a query creates confusing duplicates in the Fields pane.
  • Keep the file as .xlsx or .xlsm. Saving as .xls drops the Data Model entirely.

Practice exercise

  1. Open the course practice file, add a new sheet and type the tblEmployees table from the worked example above. Convert both ranges to named tables.
  2. Build a Data Model pivot table showing Sum of Revenue by Region, then add Name under Region.
  3. Open Data > Relationships, delete the relationship, refresh the pivot and observe what happens. Recreate it manually with Create.
  4. Add a measure Total Revenue := SUM(tblSales[Revenue]) and use it instead of the default Sum of Revenue.
  5. Copy the Sales sheet twice, rename the copies Feb and Mar, change a few dates, and append all three with Power Query into one pivot table.

Key takeaways

  • Tick Add this data to the Data Model when inserting the pivot table; that single checkbox unlocks multi-table reports.
  • A relationship needs a shared key column that is unique on the lookup (Related Table) side and the same data type on both sides.
  • Use relationships for tables with different columns and Power Query Append for sheets with the same columns.
  • Data Model pivot tables cannot use calculated fields; write DAX measures instead.
  • Most errors trace back to duplicates in the lookup table, mismatched data types or the tables being the wrong way round.

Related lessons

Frequently asked questions

Can I make a pivot table from multiple sheets without Power Pivot?

Yes. Tick Add this data to the Data Model when you insert the pivot table, then create the relationship from Data > Relationships. The Data Model engine is built into Excel 2013 and later on Windows. The Power Pivot tab is only needed for advanced work such as writing DAX measures in older versions or viewing the diagram of relationships.

Why does my pivot table show the same total for every row?

The relationship is missing or not working. Excel then repeats the grand total on every line. Open Data > Relationships and confirm a link exists between the key columns. If it exists, check that both key columns share the same data type; a numeric ID on one side and a text ID on the other never match.

Can I create a relationship on two columns at once?

No. Excel relationships use a single column on each side. If you need to match on Region and Product together, add a helper column in both tables such as =[@Region]&"|"&[@Product] and relate those combined keys. Power Query can also merge on multiple columns if you prefer to join the data before the pivot.

Does the Data Model make the workbook larger?

Slightly, because Excel stores a compressed copy of each table in the model. Compression is efficient, so a 100,000-row table usually adds a few megabytes. In return you can analyse millions of rows that would not fit on a sheet. Untick Save source data with file in PivotTable Options if size matters more than speed.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousGETPIVOTDATA in Excel: Pull Pivot Table Values into FormulasAll lessonsPivot TablesNext →PivotTable Options, Settings and Troubleshooting Guide