To create a PivotTable from multiple sheets in Excel for Microsoft 365 on Windows, combine the sheets’ data into one table with Power Query, then insert a PivotTable from that combined result. If the sheets contain different kinds of data that need to be matched, rather than stacked, load them into the Data Model and relate them before building the PivotTable.
Choose the right method for your sheets
The best method depends on what each worksheet represents. If several sheets have the same columns and each row describes the same kind of record, append the rows into one list. For example, monthly sales sheets with columns for Date, Region, Product, and Sales can become one sales table. A PivotTable can then summarize every month and region together.
If the sheets describe different but connected things, such as orders on one sheet and product details on another, do not simply stack them. Load the tables into Excel’s Data Model and connect them using a shared key, such as Product ID. A PivotTable can then use fields from both tables. This is useful when the tables have different columns or different levels of detail.
For most recurring reports with matching sheet layouts, Power Query is the practical choice. It combines data without requiring you to copy and paste rows each time. The older Multiple Consolidation Ranges option can summarize a few ranges, but it produces a limited PivotTable structure and is generally less useful for analysis.
Prepare the worksheets before combining them
Consistent source data makes the combined PivotTable easier to build and less likely to contain misleading totals. Check every sheet before importing it. The first row should contain clear, unique column names, and each subsequent row should represent one record. Avoid merged cells, blank header cells, subtotal rows inside the data, and completely empty rows or columns within the range.
For example, if a January sheet has a column called Sales Amount and a February sheet calls the same field Amount, Power Query may treat them as separate columns. Rename the headings so they match. Also check that dates are stored as dates and amounts as numbers. A value that looks like a number but is stored as text may not sum as expected in the PivotTable.
It helps to convert each source range into an Excel Table:
- Select a cell in the data and press Ctrl+T on Windows. Check that My table has headers is selected if the range includes headings.
- Choose Table Design and enter a clear name in the Table Name box, such as Sales_Jan or Sales_Feb. Table names cannot contain spaces.
- Repeat for each source range that should be combined.
Tables expand when new rows are added, which is safer than relying on fixed cell ranges. Use names that distinguish the sources but keep the column headers consistent. If the data is already an Excel Table, there is no need to create another one.
Combine matching sheets with Power Query
Power Query is Excel’s built-in tool for importing, cleaning, and combining data. In Microsoft 365 for Windows, start by selecting a cell in one of the source tables and choosing Data > From Table/Range. The Power Query Editor opens. Repeat the import for each table you need; the imported tables appear as queries in the editor.
To stack tables that share the same columns, choose Home > Append Queries > Append Queries as New. Select Three or more tables if you have more than two, add the source queries to the tables-to-append list, and confirm. The new query contains the rows from all selected sources. Append matches fields by column name, not by their position, so consistent headers matter. If one source lacks a column, the combined result can contain blank values for that field on those rows.
Review the combined preview before loading it. Confirm that expected columns are present, that records from every source appear, and that data types are sensible. Power Query may assign types automatically, but it is worth checking date, text, and numeric fields, especially if the original worksheets were not formatted consistently.
When the result looks right, select Home > Close & Load, or use the arrow beside that command and choose Close & Load To. You can load the result to a new worksheet as a table, which is straightforward for a standard PivotTable. You can also choose Only Create Connection and Add this data to the Data Model if you plan to use the Data Model. The exact choices shown can depend on how the query was created.
Build a PivotTable from the combined result
If you loaded the appended query as a worksheet table, select a cell in that table and choose Insert > PivotTable. Check that Excel has selected the full table as the source, choose where to place the PivotTable, and select OK. The PivotTable Fields pane lets you drag fields into Rows, Columns, Values, and Filters.
For example, with Date, Region, Product, and Sales columns, place Region in Rows, Product in Columns, and Sales in Values. Excel will usually summarize numeric Sales values by sum. Put Date in Filters to select a period, or add it to Rows and group dates by month or year when the values are recognized as dates. The layout is flexible: moving a field between areas changes how the same source records are summarized.
Check the Values field’s summary method. If Excel counts Sales instead of summing it, the source values may be stored as text or may include blanks and nonnumeric entries. Open the field’s settings from the PivotTable Fields pane and choose the appropriate summary, such as Sum, when the source data supports it. Do not assume a plausible-looking total proves that every source row was included.
Use the Data Model for related tables
Appending is right when the sheets contain more rows of the same type. For separate tables with related fields, load each query to the Data Model instead. For instance, an Orders table might have Order ID, Product ID, Quantity, and Date, while a Products table has Product ID, Product Name, and Category. Product details should not be repeated by stacking these tables together.
Load both tables to the Data Model, then create a relationship using their shared Product ID field. In Excel for Windows, the relationship can be managed from Data > Relationships. The matching columns need compatible data types, and the lookup side of a relationship, such as Product ID in Products, should contain unique values. Then create a PivotTable from the Data Model using Insert > PivotTable > From Data Model, if that option is available in your build, and use fields from the related tables.
A relationship does not merge the worksheets into one flat range. Instead, it lets the PivotTable combine fields while retaining the tables’ separate structures. This is a better fit for multiple related tables, but it requires sound keys and an appropriate relationship. If the tables do not share a reliable identifier, adding them to the Data Model alone will not tell Excel how their records correspond.
Refresh the report when source data changes
Once the PivotTable is built, refresh it after the source data or Power Query results change. Right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh all workbook connections and PivotTables, use Data > Refresh All. If you add rows to an Excel Table, its expanding range is generally picked up when the query refreshes.
Refreshing does not necessarily include a new worksheet that was never added to the Power Query append steps. If you create a new monthly sheet or table, edit the query and include that source, then refresh. A useful routine is to add records to the existing source tables, refresh the queries, and refresh the PivotTable before sharing the report. You can also right-click a PivotTable, choose PivotTable Options, and use the Data tab to set options such as refreshing data when opening the file, where supported by the source setup.
Common problems and practical fixes
- A sheet is missing: Confirm that its table or query is included in the append operation. Refreshing alone cannot add a source that the query does not reference.
- Two similar fields appear: Look for inconsistent column names, extra spaces, or spelling differences in the source headings. Standardize the names in the worksheets or in Power Query.
- Numbers are counted, not summed: Check whether the source column contains text-formatted numbers, blanks, or text labels. Correct the data type and refresh the query and PivotTable.
- Dates do not group by month: Check that the date column contains real date values rather than text. Correct the source or convert the column in Power Query, then refresh.
- Totals are too high: Look for duplicate source rows, repeated subtotals, or a many-to-many relationship in the Data Model. A PivotTable summarizes the records it receives; it cannot identify accidental duplicates by itself.
- New records do not appear: Make sure they are inside the source Excel Table, refresh the query, and then refresh the PivotTable. For a newly created sheet, update the query’s source list.
When totals seem wrong, compare the combined query’s row count and a simple sum with the source sheets before changing PivotTable settings. That helps distinguish a source-data problem from a layout or aggregation problem.
Alternatives and version differences
Copying all source rows into one master worksheet is a valid simple alternative when the data is small and changes rarely. It is easy to understand, but each update requires careful manual copying and can introduce omissions or duplicate records. Power Query is more repeatable and preserves a clear path from sources to the report.
Excel also has a legacy Multiple Consolidation Ranges PivotTable wizard in some desktop versions. It combines ranges into a basic summary, but typically offers generic row and column fields rather than the original descriptive columns. It is not equivalent to a modern PivotTable built from a well-structured combined table. For most users, append with Power Query or a Data Model is more flexible.
On Mac, Excel’s Power Query features and menus can differ from Windows, and availability depends on the Microsoft 365 build. If the append or Data Model commands described above are not present, use a current desktop version with those features or combine the source data into one table first. Excel for the web can handle many workbook tasks, but the full Power Query authoring workflow is not identical to Excel desktop. Menu names and supported connection types can vary by platform.
Frequently Asked Questions
Can a PivotTable use data from multiple worksheets?
Yes. The most flexible approach for similarly structured sheets is to append their rows with Power Query and create the PivotTable from the combined result. For related tables with different columns, use the Data Model and create relationships.
Do all worksheets need the same column names?
For a clean append, they should use the same names for fields that mean the same thing. Power Query matches appended columns by name, so inconsistent headings can create separate fields instead of one unified column.
Can I add another sheet later?
Yes, but the new source must be included in the Power Query setup. Add or import its table, update the append query, and refresh the query and PivotTable. Simply adding a worksheet does not automatically add it to an existing query.
Why does Excel count my numbers instead of summing them?
The source column may contain numbers stored as text or other nonnumeric values. Check and correct the column’s data type in the source or Power Query, then refresh. You can also verify the field’s summary setting in the PivotTable.
Should I append sheets or create relationships?
Append when each sheet is another batch of the same kind of records, such as one sales list per month. Use relationships when tables describe different entities, such as orders and products, and share a key that connects them.
Related
- Compare Excel Workbooks: Methods, Steps, and Differences
- Company Cash Flow Template Excel: Build and Use It
- Commercial Invoice Template in Excel: What to Include
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline Error: Causes and FixesIf an Excel trendline will not appear, select a supported chart series and choose Chart Design > Add…
- Excel Pivot Chart: Hide Zero Values the Right WayYes. In Excel for Microsoft 365 on Windows, hide zero-value categories in a PivotChart by applying a value…
- Excel Stacked Bar Chart: Reverse Series or Category OrderYes. In Excel for Microsoft 365 on Windows, you can reverse the order of the colored segments by…
- Excel Copy Trendline Equation to a Cell: What WorksYou cannot directly link an Excel chart’s displayed trendline equation to a worksheet cell with a built-in formula.…
- Excel Line Chart With Two X-Axes: Setup and AlternativesExcel can display a secondary horizontal axis on some charts, but a standard line chart does not give…
- Excel Bubble Chart Data Setup: Build a Clear ChartSet up an Excel bubble chart with three numeric columns for each data point: an X value, a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.