How to create a pivot table from multiple sheets
By the Analistable team · Updated · 2 min read
A pivot table needs one source table, so first stack the sheets into one (Power Query Append or VSTACK) and pivot that. If the sheets hold related tables (Orders and Customers), add them to the Data Model and create a relationship instead. The old Multiple Consolidation Ranges wizard still exists but gives a limited pivot.
Part of our guide: How to combine Excel sheets into one
Three approaches
| Approach | When the sheets are… | Result |
|---|---|---|
| Stack, then pivot | Same columns (one per month or region) | A full pivot with every field |
| Data Model relationships | Different tables that share a key | A pivot across related tables (Windows) |
| Multiple Consolidation Ranges | Simple label/number grids | Limited: Row, Column, Value and Page fields only |
Approach 1: stack the sheets, then pivot (recommended)
- Turn each sheet's data into a table (Ctrl+T).
- Load each with Data → From Table/Range and Close & Load To → Only Create Connection.
- Data → Get Data → Combine Queries → Append all of them, adding a column for the month or region if the sheets don't have one.
- Close & Load To → PivotTable Report.
Every field is available in the pivot, and Refresh All updates everything. In Microsoft 365 you can also stack with =VSTACK(Jan:Dec!A2:E500), but not every Excel version accepts a spilled range as a pivot source, so Power Query is the more reliable route. Background: combining sheets into one.
Approach 2: the Data Model
When the sheets are related tables rather than slices of one table:
- Format each as a table.
- Select the first and choose Insert → PivotTable, tick Add this data to the Data Model.
- In the PivotTable Fields pane, click All to see every table in the workbook.
- Drag fields from different tables. When Excel asks, click Create to define the relationship (for example Orders[Customer ID] → Customers[Customer ID]), or set it up in Data → Relationships.
The lookup table (Customers) must have unique keys. The Data Model isn't available in Excel for Mac.
| Customers[Country] | Sum of Orders[Amount] |
|---|---|
| Denmark | 75 |
| France | 200 |
Approach 3: Multiple Consolidation Ranges
Press Alt, D, P to open the classic PivotTable and PivotChart Wizard, choose Multiple consolidation ranges, and add each sheet's range. Excel builds a pivot with generic Row, Column, Value and Page fields. It only works when the first column holds labels and the rest are numbers, so it's mainly useful for old-style summary grids.
Common problems
- Blank rows in the source become “(blank)” items. Filter them out in Power Query.
- Numbers stored as text can't be summed. Set the column type to Decimal Number in Power Query.
- Duplicate keys in a lookup table prevent creating a relationship. Remove duplicates first.
Need to combine two existing pivot tables rather than their sources? See combining two pivot tables.
Frequently asked questions
- Can a pivot table use data from multiple sheets?
- Yes: stack the sheets into one table first, use the Data Model to relate tables, or use the Multiple Consolidation Ranges wizard (Alt, D, P).
- How do I add a second sheet to an existing pivot table?
- Append the second sheet to the pivot's source query in Power Query, or add both tables to the Data Model and create a relationship.
- Does the Data Model work on a Mac?
- No. Excel for Mac doesn't support the Data Model. Stack the sheets with Power Query and pivot the result instead.