Power Query Problems

How to Automatically Combine Excel Worksheets Using Power Query

Steve K Sep 28, 2026 869 views

Question details

The user wants to combine multiple Excel worksheets with identical layouts into a single master sheet that updates automatically when new sheets are added.

How to Automatically Combine Excel Worksheets with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating monthly or categorical data from multiple identically formatted worksheets into one master summary sheet.
Observed behavior: The goal is to set up a query or dynamic formula that merges existing worksheet data and automatically includes any new worksheets added in the future without manual reconfiguration.

Before you start

Ensure all the worksheets you want to combine have the exact same column headers, data types, and structural layout to prevent alignment errors during consolidation.

Solution 1Recommended

Use Power Query to Combine Worksheet Tables

The most robust method for dynamically merging multiple worksheets with the same structure into a single table.

By utilizing the Excel.CurrentWorkbook() function inside Power Query, you can append all tables in the current file. When you add new tables to the workbook, a simple refresh will include them in your master sheet.

Format data as Tables

Select the data ranges in each worksheet and press Ctrl + T to convert them into Excel Tables. Give them consistent names.

Create a Blank Query

Navigate to the Data tab, click 'Get Data', select 'From Other Sources', and choose 'Blank Query'.

List all workbook tables

In the Power Query Editor formula bar, type =Excel.CurrentWorkbook() and press Enter to list all tables in your file.

Filter and Expand

Click the dropdown on the 'Name' column to filter out the tables you don't want to combine. Then, click the Expand icon on the 'Content' column to reveal the combined data.

Load and Refresh

Click 'Close & Load' to output the combined data to a new master sheet. When new tables are added, simply go to the Data tab and click 'Refresh All'.

Use Power Query to Combine Worksheet Tables

Avoid Self-Referencing Loops: Make sure your query filters out the name of the newly created master table, otherwise Power Query will endlessly append the master table to itself upon refreshing.

Solution 2

Use VSTACK and 3-D References

A quick, formula-based approach to vertically stack data from multiple sheets in modern spreadsheet versions.

Combine Sheets Quickly

Combine Worksheets Without Complex Queries in WPS Office

Instead of writing complex Power Queries or 3-D formulas, you can use the built-in 'Merge' tool in WPS Spreadsheet to combine multiple worksheets into one with just a few intuitive clicks.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the worksheets you wish to combine.
2. Access the Merge tool: Navigate to the 'Data' tab on the top ribbon and click on the 'Merge' or 'Consolidate' button.
3. Select merge options: Choose 'Merge multiple worksheets into a single worksheet' from the dropdown menu.
4. Confirm and generate: Check the boxes next to the sheets you want to combine, confirm your header rows, and click 'OK'. A new consolidated master sheet will be created instantly.

One-click built-in tool to merge multiple worksheets instantly100% compatible with Microsoft Excel (.xlsx, .xls) filesNo advanced database or Power Query knowledge requiredLightweight application that runs smoothly even on older hardware

microsoft office alternative - wps office

Frequently Asked Questions

Why is Power Query not finding my newly added worksheets?

If you use the Excel.CurrentWorkbook() method, Power Query looks for Excel Tables, not raw worksheet ranges. Ensure you convert the data in your new worksheet into a Table (Ctrl+T) so the query can detect it.

Can I combine worksheets from different workbook files?

Yes. Instead of combining sheets from the current workbook, you can place all files in a single folder, then use 'Get Data' > 'From File' > 'From Folder' in Power Query to append the contents of all files at once.

Does the VSTACK method automatically handle mismatched columns?

No. VSTACK simply stacks data based on cell position. If Column B is 'Revenue' in Sheet 1 and 'Date' in Sheet 2, the data will be mixed. Power Query is much better at matching data by column headers regardless of their position.

Excel & Data Problems