Power Query Problems

Combine Excel Worksheets and Remove Duplicates using Power Query

Muhammad Talha Sep 28, 2026 869 views

Question details

The user needs to consolidate monthly data from multiple worksheets into a single summary sheet, eliminating duplicate person records while calculating the total for each payment category.

How to Combine Excel Worksheets and Remove Duplicates with Power Query

Product: Excel 2019
Device & OS: not provided
Scenario: Consolidating monthly financial or operational data sheets into a single, deduplicated summary with aggregated totals.
Observed behavior: The goal is to generate a single summary worksheet displaying unique individuals and their total aggregated payments from all appended monthly sheets.

Before you start

Before combining your worksheets, format the data range in each monthly sheet as an official Excel Table (by pressing Ctrl+T) and ensure all tables use identical column headers to guarantee a smooth appending process.

Solution 1Recommended

Use Power Query to Append and Group Data

The most efficient way to combine worksheets and aggregate totals in Excel 2019 is by utilizing the built-in Power Query tool.

Power Query allows you to extract data from multiple sheets, append them into a single master table, and group the data by specific criteria (like a person's name) to sum up values and remove duplicates. This method is highly automated; once set up, it can be easily refreshed when new data is added.

Import Data from Workbook

Open a new or existing workbook. Go to the 'Data' tab on the ribbon, click on 'Get Data', select 'From File', and then choose 'From Workbook'. Select your file containing the monthly sheets.

Select and Transform Data

In the Navigator window, select the multiple items (monthly tables or sheets) you wish to combine, then click 'Transform Data' to open the Power Query Editor.

Append the Queries

If the sheets loaded as separate queries, go to the 'Home' tab in the Power Query Editor and click 'Append Queries'. Choose to append them as a new query and add all your monthly tables to the 'Tables to append' list.

Group By Person to Remove Duplicates and Sum

Select the column containing the person's name. Click 'Group By' on the Home tab. In the dialog box, set the 'New column name' to Total Payments, choose 'Sum' as the Operation, and select the payment category column. This aggregates the totals and automatically removes duplicate names.

Load to Summary Sheet

Once the data looks correct, click 'Close & Load' on the Home tab. The consolidated, deduplicated summary will be imported into a new worksheet.

Use Power Query to Append and Group Data

Tip for Future Updates: Formatting each monthly range as an Excel Table before running the query makes it much easier to maintain. Next month, simply add the new table to your source file, right-click your summary sheet, and select 'Refresh'.

Combine Worksheets Easily in WPS Spreadsheet

WPS Spreadsheet offers an intuitive 'Consolidate' tool that allows you to quickly combine data from multiple worksheets, remove duplicate entries, and calculate totals without needing to build complex Power Queries.

1. Prepare a Summary Sheet: Open your workbook in WPS Spreadsheet and create a new blank worksheet where you want your summary to appear.
2. Open the Consolidate Tool: Navigate to the 'Data' tab on the top ribbon and click on the 'Consolidate' button.
3. Choose the Sum Function: In the Consolidate dialog box, ensure 'Sum' is selected in the Function drop-down menu so it can total your payment categories.
4. Add Data Ranges: Click into the 'Reference' box, select the data range from your first monthly sheet, and click 'Add'. Repeat this for all the monthly worksheets you want to combine.
5. Merge and Deduplicate: Check the boxes for 'Top row' and 'Left column' under 'Use labels in' so the tool matches data by person names. Click 'OK' to instantly generate your deduplicated summary.

Intuitive UI for combining multiple worksheetsBuilt-in sum and deduplication calculationsHighly compatible with Microsoft Excel (.xlsx) formatsFree and lightweight alternative to complex data tools

microsoft office alternative - wps office

Frequently Asked Questions

What happens if my monthly sheets have different column headers?

Power Query is strictly case-sensitive and relies on exact column header matches to align data. If the headers differ between sheets, the query will create separate columns for the mismatched names, resulting in null values. Ensure all headers are identical before appending.

Can I use formulas instead of Power Query to combine this data?

Yes, you can use a combination of VSTACK (in newer Excel versions) and UNIQUE/SUMIFS formulas to achieve this. However, Power Query is generally more robust for handling large datasets and automated monthly reporting.

How do I update the summary sheet when I add a new month's data?

If you used Power Query and imported data from a folder or updated the source workbook, you simply need to go to your final summary table, right-click anywhere inside it, and select 'Refresh'. The query will automatically pull in the new data, remove duplicates, and update the totals.

Excel & Data Problems