Power Query Problems

How to Create a Summary Table from Multiple Excel Worksheets

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to extract a specific row from approximately 30 different worksheets and combine them into a single summary table that includes the sheet names and four data columns.

Product: Excel
Device & OS: not provided
Scenario: Consolidating uniform data from dozens of individual worksheets into a single master summary view.
Observed behavior: Requires an automated method to pull row data and sheet names without manually copying and pasting from each tab.

Before you start

Ensure all your worksheets have a consistent layout, meaning the data you want to extract is located in the exact same row and columns across every sheet.

Solution 1Recommended

Use Power Query to Combine Worksheets

Power Query provides a robust, automated way to extract specific rows from multiple sheets and dynamically include the sheet names.

Power Query can seamlessly loop through all tabs in a workbook. By utilizing the Excel.Workbook function, you can extract your target row from each sheet and expand the required columns into a clean summary table.

Load the Workbook

Open a blank Excel file, navigate to the Data tab, select 'Get Data' > 'From File' > 'From Workbook', and choose your target file.

Transform Data

In the Navigator window, select the entire workbook folder instead of individual sheets, then click 'Transform Data' to open the Power Query Editor.

Select the Target Row

Use the Excel.Workbook function to read each sheet's contents. Filter or drill down into the 'Data' column to isolate your target row (for example, using [Data]{7} to select the seventh row).

Expand Columns

Click the expand icon on the Data column and select Column1 through Column4 to reveal your four data columns alongside the sheet names.

Load to Worksheet

Click 'Close & Load' to output the combined summary table into a new worksheet.

Solution 2

Use VSTACK and HSTACK Functions (Excel 365)

If you are using Excel 365, you can dynamically stack arrays using 3-D references without needing to open Power Query.

Combine Multiple Worksheets Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools, including advanced dynamic array functions and data consolidation features, allowing you to quickly summarize data from dozens of sheets into one master table.

1. Open your Workbook: Launch WPS Spreadsheet and open the file containing the worksheets you want to combine.
2. Create a Summary Sheet: Add a new blank worksheet at the beginning of your workbook to act as the master summary table.
3. Apply Array Formulas: Use supported dynamic array formulas like VSTACK to reference 3-D ranges across multiple sheets (e.g., Sheet1:Sheet30) instantly.
4. Use Data Consolidation: Alternatively, navigate to the Data tab and use the 'Consolidate' tool to merge and calculate ranges from various sheets quickly.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Supports advanced dynamic array functions like VSTACK and HSTACK for instant data stacking.Lightweight and fast, even when handling heavy workbooks with dozens of sheets.Free to use with a user-friendly, tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my Power Query expanded columns showing errors?

Errors usually occur if the data types in the target row differ across sheets or if the referenced row number does not exist in one of the worksheets. Ensure all sheets share the exact same structural layout.

Can I combine sheets without using complex formulas or Power Query?

Yes, for simpler numeric aggregations like sums or averages, you can use the built-in 'Consolidate' feature found under the Data tab. However, for extracting exact text and combining specific rows directly, Power Query or array formulas are recommended.

What does the HSTACK function do?

HSTACK horizontally appends arrays or ranges. In this scenario, it takes the vertical list of sheet names (generated by VSTACK) and places it side-by-side with the vertical list of extracted data rows.

Will VSTACK update automatically if I add a new sheet?

Yes, if you use a 3-D reference like Sheet1:Sheet30, inserting a new sheet between Sheet1 and Sheet30 will automatically include that new sheet's data in the VSTACK results.

Excel & Data Problems