Excel: How to Add Multiple Sheets to a PivotTable

How-to basics · Updated November 22, 2026

To use data from multiple sheets in one Excel PivotTable, combine the sheets into a single table first—usually with Power Query—then build the PivotTable from that combined result. Excel does not automatically treat several worksheet tabs as one PivotTable source just because they are in the same workbook.

Why a PivotTable needs a combined source

A standard PivotTable is built from one source range, Excel Table, or Data Model. When records are split across sheets, such as January, February, and March sales, the PivotTable cannot simply select all three tabs as though they formed one continuous list. The records need to be combined, or the separate tables need to be related through the Data Model.

For sheets with the same columns, stacking the records into one list is generally the clearest approach. For example, each sheet might contain Date, Region, Product, and Sales. Appending the rows produces one source with those four fields and a new row for every transaction. The PivotTable can then summarize sales by region, product, month, or any combination of those fields.

Keep one header row and one record per row in the combined data. Avoid merged cells, subtotal rows, and blank rows inside the data. Those layout choices may look tidy on a report sheet, but they make the source harder to import and summarize reliably.

Best method for matching sheets: Power Query

Power Query is the recommended method when several sheets have the same basic structure. It can append rows from separate tables, preserve the source data, and refresh the combined result after the original sheets change. Appending means placing one table’s rows beneath another; it is different from merging, which joins columns using a matching key.

For a workbook with three monthly sheets, for instance, append the January, February, and March tables. The resulting query contains all three months’ records. A useful improvement is to add a Month or Source field before appending, so the PivotTable can identify which sheet each row came from. Otherwise, a date field may provide the month if it is complete and reliable.

Prepare the worksheets

Before combining data, make each source sheet consistent. The column names should match, and each column should hold the same kind of information on every sheet. If one sheet calls the field Customer and another calls it Client, rename one so the names agree. Confirm that dates are dates, amounts are numbers, and identifiers are stored consistently.

  1. On each sheet, select a cell in the data and press Ctrl+T on Windows to make an Excel Table. Confirm that the table has headers. On Mac, use Command+T, or choose Insert > Table.
  2. Give each table a distinct, descriptive name. Select the table and use Table Design > Table Name, such as Sales_January or Sales_February.
  3. Remove title lines or totals that sit inside the record area. Keep explanatory notes outside the table.
  4. Check that every table has the same fields. Column order can differ, but consistent names and data types make the append operation easier to inspect.

Excel Tables expand when you add records directly below them, which is helpful for ongoing data entry. They also give Power Query a defined source instead of a range that might accidentally include a heading or omit newly added rows.

Append the tables with Power Query

In Excel for Microsoft 365 on Windows, load each table into Power Query, then append the resulting queries. Menu wording can vary slightly with updates, but the following workflow uses the Power Query Editor included with Excel.

  1. Select a cell in the first table and choose Data > From Table/Range. In Power Query Editor, check the column names and data types. Rename the query in the Query Settings pane if needed.
  2. Choose Home > Close & Load > Close & Load To. Select Only Create Connection, then confirm. Repeat for each source table. This creates queries without adding separate copies of every table to worksheets.
  3. Open Power Query Editor from Data > Get Data, then use Home > Append Queries > Append Queries as New. Choose Three or more tables, add each monthly query to the tables to append list, and select OK. For exactly two sources, choose the two-table option instead.
  4. Inspect the new query. Confirm that all expected rows and columns appear, and check for errors or unexpected null values. Rename the query to something recognizable, such as AllSales.
  5. Choose Home > Close & Load > Close & Load To. Load the appended result as a table on a new worksheet, or load it to the Data Model if that is the intended PivotTable source.

When loaded as a worksheet table, select a cell in the combined result and choose Insert > PivotTable. Check the suggested table or range, choose where to place the report, and select OK. Drag fields into Rows, Columns, Values, and Filters in the PivotTable Fields pane. For example, put Region in Rows and Sales in Values to see sales totals by region.

Refresh the combined data and PivotTable

The query and the PivotTable are separate refreshable objects. After adding records to a source table, choose Data > Refresh All to update the Power Query result and connected PivotTable. If the PivotTable still shows old values, right-click inside it and choose Refresh. A PivotTable refresh alone may not first update every upstream query, so Refresh All is the safer choice for this workflow.

New rows are included when they are added to the source Excel Tables. A newly created worksheet or table is not necessarily added to an existing append query automatically: edit the query and include the new source. If the monthly process repeats, use predictable table names and review the query’s source list when a new month is introduced.

When to use the Data Model instead

Appending works best when the sheets represent the same kind of records and share a common set of fields. If they contain different kinds of information, such as a Sales table and a Products table, putting all rows into one list is usually the wrong model. Load the tables to the Data Model and relate them using a shared key, such as ProductID.

In Excel for Microsoft 365 on Windows, a table can be loaded through Power Query with the option to add the data to the Data Model, or added through the Power Pivot tools where available. Create a PivotTable using the workbook Data Model as its source, then use fields from the related tables. The key values must correspond, and the relationship must be valid; a product description that merely looks similar is not a dependable join key.

Use the Data Model when you need fields from related tables without physically repeating them on every transaction row, or when the combined dataset is too large for a normal worksheet table. It adds modeling concepts and setup, so for a few similarly structured sheets, a simple append is usually easier to manage.

The older Multiple Consolidation Ranges option

Excel for Windows also has a legacy PivotTable and PivotChart Wizard that can create a PivotTable from multiple consolidation ranges. It can be useful for a quick summary of similarly shaped ranges, but it is less flexible than a normal PivotTable built from a clean, appended table. The result may use generic row and column fields rather than the original descriptive field names, which limits filtering and analysis.

To open the legacy wizard in desktop Excel for Windows, press Alt+D, then P. Choose Multiple consolidation ranges and follow the wizard prompts to select ranges. This is not the same as appending records with Power Query, and it is not the best route when you need a PivotTable with fields such as Region, Product, and Date available individually. The wizard shortcut and availability differ on Mac; Power Query or a combined source table is the more transferable approach.

Common problems and how to fix them

  • A field is missing: Check whether one source table uses a different header name. Power Query may treat differently named columns as separate fields, leaving blanks in rows from the other tables.
  • Numbers appear as text: One sheet may store amounts as text or include symbols inconsistently. Correct the source or set the column’s data type in Power Query, then refresh.
  • The PivotTable shows duplicate categories: Values that look alike may contain trailing spaces or differ in spelling, punctuation, or data type. Standardize the source values before summarizing.
  • Rows are missing after refresh: Confirm that the records are inside the source Excel Table and that the query includes that table. Check filters applied in Power Query as well.
  • Totals are unexpectedly high: Look for repeated records and confirm that the query appends each source only once. Appending adds rows; it does not automatically identify or remove duplicates.
  • The PivotTable does not update: Use Data > Refresh All, then check that the PivotTable is based on the appended output or Data Model rather than one original sheet.

Which approach should you choose?

Choose Power Query append when the sheets contain the same type of records and you want an updateable combined list. Choose the Data Model when separate tables describe related entities and should remain separate. Use the legacy consolidation wizard only for a basic summary where its limited field structure is acceptable. Copying and pasting all rows into one worksheet can work for a one-time task, but it is easy to miss later records and creates a manual maintenance step.

Excel for the web can work with PivotTables and many workbook features, but Power Query authoring and refresh capabilities are not identical across all Excel platforms and data sources. If the query-editing commands in the Windows steps are unavailable in your web or Mac edition, create or modify the query in desktop Excel for Windows, then check that the resulting workbook refreshes as required in the environment where it will be used.

Frequently Asked Questions

Can I create one PivotTable from multiple sheets without combining them?
Yes, the legacy Multiple Consolidation Ranges wizard can summarize multiple ranges, but its output is more limited than a standard field-based PivotTable. For flexible analysis, append matching sheets with Power Query or use related tables in the Data Model.

Do all sheets need identical columns?
They should have matching field names and compatible data types for a clean append. Power Query can append tables with different columns, but fields missing from a particular table will be blank for its rows. Decide deliberately whether those blanks are meaningful before building the report.

Will a PivotTable automatically include a new monthly sheet?
No. A new sheet or table is not automatically part of an existing query just because it is in the same workbook. Add it to the Power Query append setup, then use Refresh All. If you routinely add months, plan a repeatable source structure.

Can I use sheets with different layouts?
Sometimes, but first transform them into a consistent structure in Power Query. For example, rename equivalent columns, remove title rows, and reshape cross-tab reports into a record-based table. If the sheets contain different entities rather than the same records, consider Data Model relationships instead of appending.

What is the difference between append and merge?
Append stacks rows from one or more tables. Merge matches rows using one or more key columns and brings fields together across columns. Monthly sales tables usually need an append; sales and product lookup tables may need a relationship or merge, depending on the analysis.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.