Power Query Problems

How to Combine Excel Tables from Multiple Worksheets into One Summary

Partner Editor Oct 12, 2026 868 views

Question details

The user needs to consolidate similarly structured data from more than 15 Excel worksheets into a single, automatically updatable summary without manually referencing individual cells.

How to Combine Excel Tables from Multiple Worksheets into One Summary

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating monthly, departmental, or company-specific data tables scattered across multiple sheets into one unified master table to create a PivotTable summary.
Observed behavior: Manual consolidation requires repetitive copying and pasting, and direct cell references are difficult to maintain. An automated method like Power Query or dynamic array functions is required to keep the master summary updatable when source data changes.

Before you start

Ensure that all source tables share the exact same column structure (identical column headers and data types) and consider adding a custom identifier column, such as 'Company Name' or 'Month', to each table so you can track where the consolidated data originated.

Solution 1Recommended

Use the VSTACK Function to Append Tables

This is the fastest method for modern spreadsheet users, utilizing dynamic array formulas to stack data vertically in real-time.

The VSTACK function dynamically appends arrays or ranges vertically. When your source data changes, the VSTACK output updates instantly without requiring a manual refresh.

Prepare Source Tables

Add a new column (e.g., 'Company') to each of your 15+ source tables to identify the data source. Format your data as official Excel Tables by pressing Ctrl+T on each sheet.

Apply the VSTACK Formula

Create a new worksheet for your summary. In cell A1, enter the formula: =VSTACK(Table1[#All], Table2[#Data], Table3[#Data]). You can reference as many tables or sheet ranges as needed.

Create a PivotTable

Select the newly generated dynamic array range, navigate to the 'Insert' tab, and click 'PivotTable'. Use this combined dataset to build your category and score summaries.

Use the VSTACK Function to Append Tables

Real-time Updates: Because VSTACK is a live formula, any rows added, modified, or deleted in the original worksheets will immediately reflect in your combined summary sheet.

Solution 2

Consolidate Data Using Power Query

Ideal for handling a massive number of worksheets where advanced data transformation and cleansing are needed prior to summarization.

Effortlessly Consolidate Multiple Sheets in WPS Spreadsheet

WPS Office provides a highly capable, free alternative for managing complex datasets. With built-in Data Consolidation tools, support for dynamic arrays, and seamless PivotTable integration, you can merge dozens of worksheets in minutes.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your multiple data worksheets.
2. Use Data Consolidation: Navigate to the 'Data' tab and click the 'Consolidate' button. Choose the function you need (like Sum or Average) and add the ranges from your different worksheets.
3. Generate Summary: Confirm the consolidation settings. WPS Spreadsheet will automatically map and combine the data into your master summary page, ready for further PivotTable analysis.

Built-in 'Consolidate' tool under the Data tab for easy numerical aggregation across multiple sheets.High compatibility with Microsoft Excel (.xlsx) formats and standard formula syntax.Create and manage PivotTables to quickly extract summaries from combined datasets.Lightweight, fast performance even when handling complex cross-sheet references.

microsoft office alternative - wps office

Frequently Asked Questions

Do I have to format my data as Tables before combining?

While not strictly required for standard formulas, formatting your source data as official Tables (using Ctrl+T) is highly recommended. It ensures that any newly added rows expand the table range automatically and are caught by your summary formula or Power Query.

How do I know which worksheet the data came from after it is combined?

Before you combine the data, add a new column to each worksheet (e.g., 'Source' or 'Company'). Fill this column with the respective sheet name or identifier so that you can filter the data by its origin in your final PivotTable.

Will my combined master table update automatically?

If you use the VSTACK formula, the combined data will update automatically the moment a source cell changes. If you use Power Query, you must right-click the combined table or PivotTable and select 'Refresh' to fetch the latest changes.

Can I combine sheets if the columns are in a different order?

The VSTACK function requires columns to be in the exact same order across all sheets. If your columns are mixed up, Power Query is the better option because it appends data by matching the column header names, regardless of their order.

Excel & Data Problems