Layout and values Updated November 2026

Pivot from multiple sheets

Turn each sheet into a Table, load each with Data, From Table/Range, then Home, Append Queries as New, and Close & Load To a PivotTable. For sheets with different structures (orders and a product list), load them to the Data Model and create a relationship instead of appending.

Syntax

Data > From Table/Range (each sheet) > Home > Append Queries as New > Close & Load To > PivotTable Report

Part	What it is
`Append`	Stacks tables with the same columns into one long list
`Relationship`	Links two different tables on a key column in the Data Model
`Multiple Consolidation Ranges`	Legacy wizard (Alt, D, P); only gives Row, Column and Value fields

Examples

Sheets Jan, Feb, Mar each with Date, Region, Revenue
Append the three queries > load to pivot
Rows: Region. Values: Sum of Revenue

Result: Revenue per region across all three months in one table.

Orders (Order ID, Product ID, Units) and Products (Product ID, Category)
Add both to the Data Model > Data > Relationships > Orders[Product ID] to Products[Product ID]
Rows: Products[Category]. Values: Sum of Orders[Units]

Result: Units per category without a VLOOKUP column.

Common mistakes

Columns do not line up after appending: headers differ (Rev vs Revenue), so rename them identically in each query first.
The pivot shows the same number on every row: the relationship is missing or in the wrong direction, so check Data, Relationships.
The consolidation wizard gives Row, Column, Value instead of your field names: that is its limit; use Power Query instead.

Practice Pivot from multiple sheets

These drills check your answer on live data, free, no account needed.

Marketplace by country and yearShow total Sales from the Marketplace channel only, Country down the side and year across the top.

Questions

Can I make a pivot table from two sheets without Power Query?

Yes, copy them onto one sheet or use the Data Model with a relationship. Power Query is the refreshable option.

How do I add a new sheet later?

Load it as a query and add it to the Append step, or, if the sheets are in a folder, use Get Data, From Folder so new files are picked up automatically.

Data Model pivot tableWhat Add this data to the Data Model does: relate several tables without lookups, get Distinct Count and write DAX measures. Also what you lose by using it.
Unpivot dataUnpivot a wide table (one column per month) into a long list a pivot table can use. Power Query Unpivot Columns steps and a Google Sheets formula approach.
Change data sourceUpdate the range a pivot table reads from with PivotTable Analyze, Change Data Source, or convert the source to an Excel Table so it grows by itself.