Power Query Problems

How to Combine Excel Worksheets into an Ordered Summary Table

Natalie Taylor Sep 30, 2026 869 views

Question details

Combine data from five separate Excel worksheets into one summary sheet that ignores blank rows, preserves worksheet order, and updates dynamically.

How to Combine Excel Worksheets into an Ordered Summary Table

Product: Excel
Device & OS: not provided
Scenario: Merging multiple datasets from separate tabs into a master summary table without manual copying and pasting.
Observed behavior: The user needs the final output to automatically append new rows when data is added to the source sheets while excluding any empty rows in the defined ranges.

Before you start

Ensure all source data ranges have identical column structures and consistent headers so that the append operations align the data correctly.

Solution 1Recommended

Use Power Query to Append Tables Dynamically

This is the most robust method for expanding data, as Power Query can automatically pull in all tables in a workbook, filter blanks, and output them in order.

By converting your source ranges into Excel Tables, you allow Power Query to reference them dynamically, ensuring any new rows are captured during the next data refresh.

Convert source ranges to Excel Tables

Go to each of your five worksheets, highlight the data range, and press Ctrl+T to create a Table. Give each table a logical name from the Table Design tab.

Load tables into Power Query

Navigate to the Data tab on the ribbon, click 'Get Data', select 'From Other Sources', and choose 'Blank Query'. In the formula bar, type '=Excel.CurrentWorkbook()' and press Enter.

Expand and filter the data

Click the expand icon (two diverging arrows) on the Content column header to reveal the data from all tables. To remove blank rows, click the filter arrow on your primary data column and uncheck '(null)' or '(Blank)'.

Load data to a Summary Table

Once the data is ordered and filtered, click 'Close & Load' from the Home tab in the Power Query Editor. This will generate a new Summary worksheet containing your combined table.

Use Power Query to Append Tables Dynamically

Updating the Summary: When you add new data to the original worksheets, simply right-click anywhere inside the Summary Table and select 'Refresh' to update the results.

Solution 2

Use Dynamic Array Formulas (VSTACK, LET, and FILTER)

Best for users on newer versions of Excel who prefer a formula-based approach over using Power Query for fixed ranges.

Efficient Spreadsheet Management

Combine Worksheets Easily with WPS Office

WPS Spreadsheet provides powerful tools and functions to help you consolidate worksheets, filter out blank rows, and manage large datasets efficiently. With high compatibility and advanced formula support, organizing your data is easier than ever.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the separated worksheets.
2. Format data as tables: Select the data in your sheets and use the Table feature in the Insert tab to structure your ranges.
3. Use formulas to consolidate: Use supported array formulas or the Data Consolidation tool in the Data tab to merge the tabs into a single summary sheet.
4. Save your updated file: Save your document securely in the standard .xlsx format to retain all calculations and combined data.

100% format compatibility with Microsoft Excel (.xlsx) filesSupports dynamic array formulas for instant data consolidationBuilt-in robust Data tools for merging and analyzing datasetsLightweight, fast performance for heavy data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why are my blank rows not being ignored when combining sheets?

If you are simply appending ranges directly, blank rows are included by default. You must explicitly filter them out using the FILTER function in your formulas, or by deselecting 'null' values in the Power Query editor before loading the final table.

Will the summary table update automatically when I add new data?

If you use dynamic array formulas like VSTACK, the summary updates instantly. However, if you use the Power Query method, you need to navigate to the Data tab and click 'Refresh All' (or right-click the table and select Refresh) for the new rows to appear.

Do my source worksheets need to have identical column layouts?

Yes. For both Power Query appending and VSTACK formulas to stack the data correctly, the columns should ideally be in the exact same order and format across all sheets. Mismatched columns can lead to misaligned data in your summary table.

Can I use the VSTACK function in older versions of Excel?

No, VSTACK is a newer dynamic array function available only in Microsoft 365 and Excel 2021 or later. If you are using an older version, the Power Query method is highly recommended instead.

Excel & Data Problems