Pivot Table from Multiple Sheets in Excel (combine first, then pivot)

A pivot reads one table. When your data is split across sheets — Jan, Feb, Mar — stack them into one table first, then pivot. Power Query does the stacking and refreshes it.

Before you start

Every sheet needs the same headings in the same order. Make each one an Excel Table (Ctrl + T) and name them: Jan, Feb, Mar (Table Design › Table Name).

Steps

1. Click inside the Jan table › Data › From Table/Range. Power Query opens. Home › Close & Load To › Only Create Connection.
2. Repeat for Feb and Mar.
3. Data › Get Data › Combine Queries › Append › Three or more tables › add Jan, Feb, Mar › OK.
4. In the editor, Home › Close & Load. A new sheet holds all rows in one table.
5. Click in that table › Insert › PivotTable. Build as normal.

When data changes

Add rows to any month sheet, then Data › Refresh All. The combined table and the pivot both update.

Add a Month column

In Power Query before appending, for each query: Add Column › Custom Column › "Jan". Then the pivot can filter by month.

Older Excel without Power Query

Copy-paste the sheets under each other on a Combined sheet, then pivot. Manual, but works everywhere.

Check it worked

Combined table row count = Jan + Feb + Mar row counts. The pivot’s Grand Total equals the three sheets’ totals added.

Common mistake

A heading spelled differently on one sheet (“Amount” vs “Amt”) creates two columns. Fix the heading and refresh.