How to Make a Pivot Table From Multiple Sheets in Excel

To build a pivot table from multiple sheets, you first give the PivotTable one source to read. There are two good ways: append sheets with the same columns into one table using Power Query, or load different but related tables into the Data Model and connect them. This guide shows both, step by step, and helps you pick the right one for your data.

Which method should you use?#

The right method depends on what your sheets look like. Ask one question: do the sheets have the same columns?

Most people with "one sheet per month" data need the first method. If you only need a total by matching labels and don't need a pivot, the guide to combining data from multiple sheets covers Consolidate, VSTACK and copy-paste.

Before you start: turn each sheet into a table#

Excel tables make both methods more reliable, because a table grows when you add rows. Do this on every sheet:

1. Click inside the data.
2. Press Ctrl+T (Mac: ⌘T) and confirm that My table has headers is ticked.
3. On the Table Design tab, give it a clear name, such as Sales_Jan.

Check that the headers match exactly across sheets. "Sales" on one sheet and "Sales Amount" on another will not line up. Also remove blank rows and subtotal rows inside the data.

Method 1: Append the sheets with Power Query, then pivot#

Power Query is Excel's tool for importing and reshaping data in steps that you can refresh. Appending stacks tables on top of each other, so three monthly tables become one long table. It is available in Excel 2016 and later on Windows and in recent Microsoft 365 versions for Mac. For a first look at the tool, see Power Query for beginners.

Step 1: Load each table as a query#

1. Click inside the first table and go to Data > From Table/Range.
2. The Power Query Editor opens. Choose Close & Load > Close & Load To…
3. Pick Only Create Connection and click OK.
4. Repeat for each sheet's table.

Step 2: Add a column that says where each row came from#

If the tables don't already have a Month or Store column, you lose that detail when you stack them. In each query, use Add Column > Custom Column and type a fixed value, such as "Jan". Or add the column in the source tables before you start.

Step 3: Append and build the PivotTable#

1. Go to Data > Get Data > Combine Queries > Append.
2. Choose Three or more tables, and add every monthly query.
3. Click OK, check the preview, then choose Close & Load To…
4. Pick PivotTable Report and click OK.

You now have one PivotTable built from every sheet. Drag Region to Rows, Month to Columns and Sales to Values. With the exercise data, North shows 12,700: 4,200 in January, 3,900 in February and 4,600 in March.

Method 2: Use the Data Model for related tables#

The Data Model is a store inside the workbook that holds several tables and the links between them. Use it when your sheets hold different information that shares an ID, such as a Sales sheet with a ProductID column and a Products sheet with ProductID and Category.

1. Format both sheets as tables, as above.
2. Click inside the Sales table and go to Insert > PivotTable.
3. Tick Add this data to the Data Model, and click OK.
4. In the PivotTable Fields pane, click All to see every table in the workbook.
5. Drag Category from Products to Rows, and Revenue from Sales to Values.
6. If Excel says a relationship may be needed, click Create. Set Sales → ProductID as the column and Products → ProductID as the related column.

You can also set relationships first, under Data > Relationships. The lookup table (Products) must have each ID only once, or the relationship fails.

Data Model features, including relationships, are most complete in Excel for Windows. If you don't see these options on a Mac, use Method 1 instead.

Method 3: The old PivotTable Wizard#

On Windows, Excel still has the legacy PivotTable and PivotChart Wizard. Press Alt, then D, then P (one after another) to open it, and choose Multiple consolidation ranges.

It works, but the result is very limited. You only get generic fields called Row, Column, Value and Page1, and you can't use your own column names as separate fields. Use it only for a quick cross-tab of totals. For anything you'll reuse, Methods 1 and 2 are far better.

Keep it up to date: refresh#

PivotTables don't update on their own. When someone adds rows to a monthly sheet, go to Data > Refresh All (Windows: Ctrl+Alt+F5). This reruns the Power Query append and then refreshes the pivot.

If you add a new sheet, such as April, make it a table, load it as a connection, and add it to the append query with Home > Append Queries in the Power Query Editor. The guide on how to refresh a pivot table covers refresh settings, including refreshing when the file opens.

Common problems and fixes#

These are the issues people hit most often:

• Extra columns full of blanks: headers don't match exactly. Rename them in the source tables and refresh.
• Totals look too low: some numbers are stored as text. In Power Query, set the column type to a number.
• "(blank)" in the pivot: there are blank rows in a source table. Delete them or filter them out in Power Query.
• Relationship error in the Data Model: the lookup table has duplicate IDs. Remove the duplicates first.
• Missing month: the new sheet was never added to the append.

When a formula is enough#

If you only need a few totals, a formula can be faster than a pivot. A 3D reference such as =SUM(Jan:Mar!C2) adds the same cell on every sheet between Jan and Mar; see how to sum across sheets. On a combined table, SUMIFS gives any total you need, like the exercise on this page. The cross-sheet references lesson practises pointing formulas at other tabs.

New to pivots? Start with the beginner's pivot table guide before you combine sheets.

Practise it#

Try the exercise on this page: SUMIFS gives the same North total the pivot should show, which is a quick way to check your combined data. Then practise advanced PivotTables in a live spreadsheet, where you nest fields, add filters and update the source when new rows arrive. The lesson checks each step instantly.