Power Query Problems

How to Combine Rows from Multiple Excel Sheets into a Master Sheet

Partner Editor Oct 12, 2026 869 views

Question details

The user needs to automatically combine or append rows of data from multiple worksheets into a single master Excel sheet.

How to Combine Rows from Multiple Excel Sheets into a Master Sheet

Product: Excel
Device & OS: not provided
Scenario: Consolidating data scattered across several worksheets into a single master summary sheet for easier analysis and tracking.
Observed behavior: Data is currently separated across multiple sheets, requiring a dynamic method to automatically pull and stack all rows together without manual copying and pasting.

Before you start

Ensure that all the worksheets you want to combine have identical column structures and headers to prevent data misalignment in your master sheet.

Solution 1Recommended

Use Power Query to Append Multiple Sheets

Power Query provides a robust and refreshable way to import and combine multiple worksheets into one master sheet.

Power Query is ideal for handling large datasets and multiple sheets because it creates a direct connection to your data. When you add new rows to your individual sheets, you can simply refresh the master sheet to update the consolidated data.

Convert Data to Tables

Select the data in each of your source worksheets and press Ctrl + T to convert them into Excel Tables. Name the tables logically (e.g., Table1, Table2).

Get Data

Go to the 'Data' tab on the Excel ribbon, click on 'Get Data', select 'From File', and then choose 'From Workbook'. Import the current workbook you are working in.

Transform Data

In the Navigator window, select multiple items (your tables) and click 'Transform Data' to open the Power Query Editor.

Append Queries

In the Power Query Editor, go to the Home tab and click 'Append Queries' or 'Append Queries as New'. Select the tables you want to combine and click OK.

Load to Master Sheet

Click 'Close & Load To...' on the Home tab, select 'Table', and choose to place it in a New Worksheet to serve as your master sheet.

Use Power Query to Append Multiple Sheets

Refreshable Data: Once set up, you only need to click 'Refresh All' on the Data tab to update your master sheet with any new rows added to the source sheets.

Solution 2

Use VSTACK and FILTER Dynamic Array Formulas

For users with newer Excel versions, the VSTACK function combined with FILTER can quickly stack non-blank ranges from multiple sheets dynamically.

Smart Data Consolidation

Combine Multiple Sheets Instantly with WPS Office

WPS Spreadsheet provides a built-in 'Merge Worksheets' tool that allows you to easily combine multiple worksheets into a single master sheet without writing complex VSTACK formulas or navigating Power Query.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open the workbook containing the multiple sheets you want to combine.
2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon and click on the 'Merge' or 'Merge Worksheets' button.
3. Select Merge Options: Choose 'Merge multiple worksheets into a single worksheet'. Check the boxes next to the sheets you want to include in the consolidation.
4. Execute the Merge: Specify the header row settings if needed, and click 'Merge'. WPS Spreadsheet will automatically generate a new master sheet with all your combined data.

One-click worksheet merging without complex formulasFully compatible with Microsoft Excel (.xlsx) files and arraysLightweight, fast, and free to useFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Will the master sheet update automatically when I add new data?

If you use the VSTACK and FILTER dynamic array method, the master sheet will update instantaneously. If you use Power Query, you will need to go to the Data tab and click 'Refresh All' to see the newly added rows.

Why am I getting a #CALC! or #NAME? error with the VSTACK formula?

The #NAME? error means your version of Excel does not support the VSTACK function (it requires Microsoft 365 or newer versions). A #CALC! error usually occurs if the FILTER function returns no results (e.g., if a source sheet is completely empty).

Can I combine sheets from entirely different workbooks?

Yes. Using Power Query, you can select 'From Folder' or multiple 'From File' connections to append tables located in different workbooks. Array formulas like VSTACK can also reference external workbooks, but they require the external workbooks to be open to function reliably.

How do I handle sheets with different column headers?

If your columns are in different orders or have slightly different names, Power Query is the best solution. When appending queries, Power Query automatically aligns data by column header names, leaving null values for columns that do not exist in a specific sheet.

Excel & Data Problems