How to create a pivot table from multiple sheets
By the Analistable team · Updated · 5 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.
Worked example: three regional sheets
Sheets North, South and West each hold Date, Product and Sales with the same headers. After appending them in Power Query (with a Region column added to each query first), the pivot with Product in Rows, Region in Columns and Sum of Sales in Values looks like this:
| Product | North | South | West | Grand Total |
|---|---|---|---|---|
| Chairs | 1200 | 900 | 600 | 2700 |
| Desks | 2500 | 1800 | 2100 | 6400 |
| Lamps | 300 | 450 | 250 | 1000 |
| Grand Total | 4000 | 3150 | 2950 | 10100 |
Check the pivot against the sources: the Grand Total for North (4,000) should equal =SUM(North!C:C). If it doesn't, a row was filtered out in the query or a Sales value came through as text.
Step by step in Excel for Mac and Excel 2016
On Windows, Excel 2016 and later include Power Query and the Data Model, so all three approaches are available. On a Mac, recent Microsoft 365 builds include Power Query for combining tables, but the Data Model and Power Pivot are not available, so use the stack-then-pivot route. Where VSTACK exists, a formula stack works too:
=VSTACK(North!A1:C200, South!A2:C200, West!A2:C200)Pivot tables cannot use a spilled range directly as a dynamic source in every version, so the reliable pattern is to load the Power Query result as a table and point the pivot at that table. A table grows with each refresh and the pivot follows it.
Refreshing every month
- Paste the new month's rows into each regional sheet's table (or add a new sheet and a new query).
- Choose Data → Refresh All. This refreshes the queries first and then the pivot tables that depend on them.
- If a new sheet was added, edit the Append step to include its query; Append does not pick up new queries on its own.
- Compare the Grand Total with the sum of the source totals before sending the report.
If the sheets arrive as separate workbooks, put them in one folder and use Power Query's combine-from-folder so new files are included automatically.
Which approach when
| Your sheets | Best approach | Why |
|---|---|---|
| Same columns, one per month/region | Stack, then pivot | Full pivot features, one field list |
| Different tables sharing a key | Data Model with a relationship | No lookup columns, no duplicated data |
| Simple grids with row and column labels | Multiple Consolidation Ranges | Quick, but only Row, Column, Value and Page fields |
| Very large data | Data Model | Compressed storage handles more rows than a sheet |
For the Data Model route, the relationship set-up is explained in Excel Data Model relationships.
Common mistakes
- Pointing the pivot at the first sheet and expecting the others to be included — Change Data Source accepts one range only.
- Stacking the sheets without a column that identifies the source, so the pivot can't split by region or month.
- Leaving subtotal or total rows in the source sheets; they're stacked as data and double the figures.
- Using slightly different headers ("Sales" and "Sales £"), which Append treats as two separate columns.
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.