Power Query Problems

How to Summarize Data from Multiple Excel Worksheets

Khadija Khan Oct 16, 2026 869 views

Question details

The user needs to aggregate total counts, such as tracks and discs, from a dataset spread across multiple alphabetical worksheets onto a single summary sheet.

How to Summarize Data from Multiple Excel Worksheets on a Summary Sheet

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating a fragmented collection dataset stored across multiple tabs (A-Z) into one master dashboard to calculate total metrics.
Observed behavior: Data is separated into multiple tabs, requiring an automated method to combine, filter, and total the metrics accurately without manual addition.

Before you start

Ensure that all the worksheets you intend to combine share identical column headers and data structures to prevent errors when appending.

Solution 1Recommended

Combine Worksheets Using Power Query and PivotTables

This is the most maintainable approach, especially when new records will be added or updated regularly.

Power Query allows you to append multiple tables into a single master connection. By creating a PivotTable from this combined data, you can easily summarize totals and apply filters dynamically.

Format data as tables

Go to each worksheet (A-Z), select your data range, and press Ctrl+T to convert the data into standard Excel Tables. Ensure the headers are consistent.

Load data into Power Query

Click anywhere inside your first table, go to the Data tab on the ribbon, and select 'From Table/Range'. Once the Power Query Editor opens, choose 'Close & Load To...' and select 'Only Create Connection'. Repeat this for all relevant tables.

Append queries

In the Power Query Editor or via Data > Get Data > Combine Queries, select 'Append'. Choose to append three or more tables, add all your table queries to the list, and click OK.

Load to a summary sheet

In the Power Query Editor, click 'Close & Load' to output the fully combined dataset onto a new blank summary worksheet.

Create a PivotTable

Select the newly combined data table, go to the Insert tab, and click 'PivotTable'. Drag 'Tracks' and 'Discs' to the Values area to generate your totals, and use the USB column as a filter if needed.

Combine Worksheets Using Power Query and PivotTables

Dynamic Updates: When you add new rows to your original A-Z sheets, you only need to right-click your PivotTable and select 'Refresh' to update your summary.

Solution 2

Use the Data Consolidate Command

A faster alternative if your worksheets have identical layouts and you only need simple totals without complex dynamic linking.

Summarize Multiple Worksheets Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful Data Consolidation and PivotTable features, allowing you to instantly merge and summarize data from multiple tabs without complex setups.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the multiple worksheets you want to summarize.
2. Create a summary sheet: Click the '+' icon at the bottom tab bar to add a new blank worksheet for your totals.
3. Use Data Consolidation: Navigate to the Data tab on the top ribbon and click on the 'Consolidate' button. Select 'Sum' as your function.
4. Add data ranges: Select the specific data ranges from each of your individual worksheets, click Add, and ensure you check the boxes for top row and left column labels.
5. Generate totals: Click OK to instantly merge the information and generate precise totals on your summary sheet.

Free and lightweight alternative to Microsoft OfficeSeamlessly opens, edits, and saves Microsoft Excel (.xlsx) filesBuilt-in Data Consolidate feature to instantly merge worksheet dataAdvanced PivotTables for quick and dynamic data summarization

microsoft office alternative - wps office

Frequently Asked Questions

Can I use formulas instead of Power Query to summarize data?

Yes, if the data is located in the exact same cell reference across all worksheets, you can use a 3D reference formula. For example, typing =SUM('A:Z'!B2) will sum the values in cell B2 across all sheets from A to Z.

What happens if my column names are slightly different in some sheets?

If headers do not match exactly, Power Query will create separate columns for the mismatched headers and fill the gaps with null values. It is highly recommended to standardize your column headers before appending.

Does Data Consolidation automatically update when I add new data?

It only updates automatically if you check the 'Create links to source data' box in the Consolidate dialog window. If left unchecked, the summary represents a static snapshot, and you will need to run the consolidation tool again to reflect any updates.

How do I handle newly added worksheets in Power Query?

If you manually appended specific queries, you will need to open the append step and add the new worksheet's query to the list. Alternatively, using the 'Get Data from Folder' method allows new files or sheets to be included automatically upon refreshing.

Excel & Data Problems