Power Query Problems

How to Combine Multiple Excel Worksheets into One Master Sheet

Maira Mehtab Sep 28, 2026 872 views

Question details

The user needs a method to automatically consolidate records from five different worksheets into a single master worksheet.

Product: Excel
Device & OS: not provided
Scenario: Multiple users are entering data into separate worksheets using the exact same column headings, and the data needs to be merged.
Observed behavior: Looking for an automated solution to append and consolidate the data into one master sheet without manually copying and pasting.

Before you start

Ensure all your source worksheets have exactly the same column headings and data structure. It is highly recommended to format your data ranges as Tables (Ctrl+T) before combining them.

Solution 1Recommended

Use Power Query to Append Tables

Power Query is the most efficient built-in tool for combining multiple sheets with identical columns into a single master sheet.

By loading your tables into Power Query as connections, you can append them together. This method establishes a dynamic link, meaning your master sheet can be updated automatically when new data is added to the source sheets.

Format data as Tables

Go to each of the five worksheets, select the data range, and press Ctrl+T to format them as Tables. Give each table a recognizable name in the Table Design tab.

Load Tables to Power Query

Click anywhere inside your first table. Go to the Data tab and click 'From Table/Range'. When the Power Query Editor opens, click the 'Close & Load' dropdown, select 'Close & Load To...', and choose 'Only Create Connection'. Repeat this for all five tables.

Append the Queries

In the Excel Data tab, click 'Get Data', navigate to 'Combine Queries', and select 'Append'. In the dialog box, select 'Three or more tables'.

Combine and Load

Add all five of your table queries to the 'Tables to append' box and click OK. The Power Query Editor will open showing your combined data. Click 'Close & Load' to output the consolidated records into your new master worksheet.

Auto-Refresh Capability: Whenever users add new data to the original five worksheets, simply right-click anywhere on the combined master table and select 'Refresh' to instantly update the consolidated records.

Efficient Data Management with WPS

Combine Worksheets Easily in WPS Spreadsheet

WPS Office Spreadsheet provides intuitive built-in data tools to quickly consolidate and merge data from multiple sheets, offering a seamless alternative without complex query setups.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the multiple worksheets you want to combine.
2. Access Data tools: Navigate to the 'Data' tab located on the top ribbon interface.
3. Use the Consolidate feature: Click on 'Consolidate'. In the dialog box, select the function you need (e.g., Sum, Count) and add the data ranges from your different sheets.
4. Generate the master sheet: Check 'Create links to source data' if you want automatic updates, then click OK to instantly generate your combined master sheet.

Easily consolidate multiple sheets into one master sheetFully compatible with Microsoft Excel (.xlsx) formats and formulasLightweight application with a familiar, user-friendly interfaceFree alternative with powerful data processing capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Do the column headers need to match exactly in Power Query?

Yes, to successfully append data using Power Query, the column headers must be spelled exactly the same across all worksheets. Power Query is case-sensitive, so 'Date' and 'date' would be treated as two separate columns.

What happens if I add a new worksheet later?

If you manually appended specific queries, you will need to load the new table and edit your append query to include it. Alternatively, using 'Get Data > From File > From Workbook' can automatically pick up new sheets upon refreshing.

Can I combine worksheets from completely different Excel files?

Yes. Instead of combining tables within the same workbook, you can place all source Excel files in a single Windows folder. Then, use 'Get Data > From File > From Folder' to combine all worksheets simultaneously.

Excel & Data Problems