To consolidate information shown in multiple PivotTables, combine their underlying data first, then build one PivotTable from the combined source. In Excel for Microsoft 365 on Windows, Power Query’s Append feature is usually the clearest method when the source tables have the same columns; use the Data Model when separate tables have different structures but related fields.
What “consolidate PivotTables” can mean
Excel does not provide a general command that merges several finished PivotTables into one while preserving all their fields, filters, and drill-down details. A PivotTable is a summary of its source data, so the reliable way to create a unified report is usually to consolidate the source records and create a new PivotTable from them. The existing PivotTables can remain in the workbook, but the new report is built from source data rather than from the visible cells of those summaries.
There are three common situations:
- Same columns, separate lists: Append the lists into one table. For example, combine monthly sales tables with columns for Date, Region, Product, and Amount.
- Different tables with a shared key: Keep the tables separate and relate them in the Data Model. For example, connect a Sales table to a Products table using Product ID.
- A quick summary of similarly arranged ranges: The legacy PivotTable and PivotChart Wizard can consolidate multiple ranges, but it creates a more restricted report.
First check whether you have the original source tables. Combining the source data generally gives you more useful fields and more dependable refresh behavior than copying numbers out of existing PivotTables.
Choose a method based on the source data
Use Power Query’s Append operation when each source contains the same kinds of records and the same field names. It stacks rows from one table below the rows from another. The tables do not have to contain the same number of records, but consistent column names and data types make the result easier to use. If a column is missing in one source, the combined query can still contain that column, with blank values for records that did not supply it.
Use the Data Model when each table describes a different part of the same business process. A transaction table might have one row per sale, while a customer table has one row per customer. Rather than repeating customer details in every sale record, relate the tables using a key such as Customer ID. This is a model of related tables, not a row-by-row append.
Use the legacy consolidation wizard only when its limitations suit the task—for example, when you need a basic summary of ranges with a similar layout and do not need to analyze the original fields separately. If the source tables have different field structures, or you need detailed filtering by individual attributes, Power Query or the Data Model is generally a better fit.
Combine matching tables with Power Query
Power Query is a good choice for recurring reports because it records the steps used to combine and shape the data. After the source tables are updated, you can refresh the query instead of repeating a manual copy-and-paste. For best results, make each source range an Excel Table and give the tables clear, consistent column headings.
- Click inside each source range and choose Insert > Table. Confirm that the table has headers, then give each table a distinct name on the Table Design tab, such as Sales_January and Sales_February. Use the same headings for equivalent fields.
- For each table, click inside it and choose Data > From Table/Range. Power Query Editor opens with that table as a query. If prompted, confirm the header row is being used as column names.
- In Power Query Editor, choose Home > Append Queries > Append Queries as New. Select Three or more tables if needed, add the queries to the list of tables to append, and select OK.
- Review the resulting query. Check that the columns line up by name, dates are recognized as dates, amounts are numeric, and no unwanted totals or blank rows have come through. Append matches columns by their names, not by the visual position of a column in the source.
- Choose Home > Close & Load to return the combined data to Excel. Then choose Insert > PivotTable, select the combined table or loaded query as the source, and place the PivotTable on a new or existing worksheet.
In the new PivotTable Fields pane, drag fields into Rows, Columns, Values, and Filters as appropriate. For instance, put Region in Rows, Month in Columns, and Amount in Values. If Excel counts Amount instead of summing it, check that the source values are numeric and change the calculation through Value Field Settings.
Refresh and maintain an appended report
Power Query’s advantage is repeatability, but it does not automatically discover every new source table simply because the table exists in the workbook. If you append a new month, add its query to the append step, or design the query around a consistent way of identifying all source tables. After the query is set up, refresh the combined query and its PivotTable when source records change. Use Data > Refresh All to refresh workbook connections and PivotTables together.
Keep the source tables’ headings stable. Renaming a heading can change the output schema or break later steps that refer to that field. Also avoid putting subtotals or grand-total rows into source tables: a PivotTable calculates its own totals, and imported total rows can be counted as if they were ordinary records. When adding new rows to a formatted Excel Table, the table normally expands to include them; check that the new records are inside the table before refreshing.
If separate PivotTables use different source ranges, appending their underlying lists is not the same as appending the displayed summaries. A summary may already have grouped dates, hidden detail, filters, or calculations. Rebuilding from the raw records avoids treating an old subtotal as a new transaction.
Use the Data Model for related tables
The Data Model is useful when appending would create a wide, repetitive table or when each source has a distinct role. Add the tables to the model when creating or importing them, then create a relationship between matching key columns. For a PivotTable, choose Insert > PivotTable and select the option to use this workbook’s Data Model, where available. The precise wording in the dialog can vary by Excel build.
For example, suppose Sales contains Sale Date, Product ID, and Units, while Products contains Product ID, Product Name, and Category. Relate the tables on Product ID. A PivotTable can then summarize Units by Category while using the product descriptions from the Products table, without copying Product Name and Category into every sales row.
Relationships work best when the lookup side has unique key values: in that example, each Product ID should appear once in Products. If keys are duplicated unexpectedly, blank, or formatted differently between tables, the relationship may be unavailable or produce confusing results. The Data Model is more powerful than a simple append, but it requires care with keys and table design. Some Data Model authoring features and dialogs differ on Mac; if a particular modeling option is unavailable in your Excel build, use Power Query to create a flat combined table or check the current Microsoft 365 feature availability for that platform.
Legacy option: multiple consolidation ranges
Excel retains a PivotTable and PivotChart Wizard for a basic PivotTable built from multiple consolidation ranges. On Windows, invoke the legacy wizard with Alt, D, P (press the keys in sequence). Choose Multiple consolidation ranges, then select whether to create a single page field or let the wizard create page fields. Add each range, specify where the PivotTable should go, and finish the wizard. If the shortcut is unavailable in a particular setup, you may be able to add PivotTable and PivotChart Wizard to the Quick Access Toolbar from Excel’s command customization options.
This route is not equivalent to appending tables in Power Query. Consolidation ranges are treated as a basic matrix of row labels, column labels, and values. The resulting report generally does not expose each original source field as a normal field in the PivotTable Fields pane, and the wizard’s page-field options are limited. It is best understood as a way to summarize similarly arranged ranges, not as a modern data-combining workflow. Excel for the web does not offer the same legacy wizard workflow; Mac keyboard access and available commands can also differ, so the Windows shortcut should not be assumed to work on Mac.
Do not confuse PivotTable consolidation with Data Consolidate
The command Data > Consolidate is a separate worksheet feature. It can combine values from multiple ranges using a function such as Sum or Average, optionally matching labels. It does not create a PivotTable, and its output is a consolidated result on a worksheet rather than a field-based interactive report. Use it when you need a straightforward total across ranges and do not need PivotTable fields, filtering, or drill-down. Use Power Query or the Data Model when you need a refreshable, analyzable data source.
Common problems and practical checks
- Fields appear in the wrong place or separate columns: Check the spelling and spaces in the source headings. Append aligns columns by heading, so “Sales Amount” and “Amount” are treated as different fields.
- Numbers are counted instead of summed: Check for text-formatted numbers, mixed values, or errors in the source column. Correct the type in Power Query or the source, then refresh and review Value Field Settings.
- Some records are missing: Confirm each row is inside its Excel Table or included in the query’s source range. Also inspect query filters and any steps that remove rows.
- Totals are too high: Look for duplicated records or source subtotal rows. Appending two exports that overlap in dates will include the overlapping records twice unless you remove or otherwise account for duplicates.
- The PivotTable does not show recent changes: Refresh the query and PivotTable. If the combined query is loaded to a separate worksheet table, make sure the PivotTable uses that output rather than an old fixed range.
Before relying on a consolidated report, compare a small sample against the source lists and check a known grand total. Decide whether duplicate records are legitimate, and document whether the report includes all periods, regions, and categories. A PivotTable can summarize data accurately while still answering the wrong question if a source was omitted or filtered.
Frequently Asked Questions
Can I merge two existing PivotTables directly?
Not as fully featured PivotTables with all their fields intact. Combine their underlying source records with Power Query, or model related source tables, then create a new PivotTable. Copying and stacking the visible summaries is only suitable for a simple static result and can carry forward old filters or subtotals.
Can multiple PivotTables use the same source data?
Yes. You can create separate PivotTables from the same source table or query and arrange each one to answer a different question. A shared source does not mean their layouts or filters have to be identical.
Will the consolidated PivotTable update when I add rows?
It can, if the rows are included in the source Excel Table or query and you refresh the relevant data. Use Data > Refresh All as a practical check. A PivotTable based on a fixed cell range may not include rows added beyond that range.
Can I combine tables with different columns?
Power Query can append tables that do not have identical columns, but the output will include the union of their column names and blanks where a source lacks a field. If the tables represent different entities linked by IDs, a Data Model relationship may be more appropriate than appending them.
What is the quickest method for a one-time total?
If you only need a basic total from similarly laid-out ranges, Data > Consolidate may be quicker. If you need to filter by fields or refresh the result as records change, combine the source data with Power Query and build a PivotTable instead.
Related
- Can a Corrupted Excel File Be Fixed? Recovery Steps
- Can ChatGPT Compare Two Excel Sheets?
- Goal Seek in Excel: Steps, Examples, and Calc Differences
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart: How to Remove a SeriesTo remove a series from an Excel PivotChart, select the chart, open PivotChart Analyze > Field List, and…
- Excel Waterfall Chart Inputs and SetupExcel waterfall chart inputs are a list of categories and one matching list of numeric changes or balances:…
- Excel Pie Chart: Show Percentages ClearlyYes. In Excel for Microsoft 365 on Windows, select the pie chart, then choose Chart Design > Add…
- Excel Fill Area Under a Line Chart: Add Shaded AreaTo fill the area beneath a line in Excel for Microsoft 365 on Windows, create a combo chart…
- Pivot Table Spill Error: Causes and FixesA PivotTable spill error usually means a dynamic-array formula cannot place its results because the intended spill range…
- Google Sheets Chart Templates: How to Find and Use ThemGoogle Sheets does not have a dedicated gallery of reusable chart-style templates, but you can start with a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.