Power Query Problems

How to Merge Two Excel Sheets Without Duplicate Rows Using Power Query

Natalie Taylor Oct 23, 2026 869 views

Question details

The user needs to combine data from two separate Excel worksheets, containing over 13,000 rows each, into a single new sheet while automatically stripping out duplicate records.

How to Merge Two Excel Sheets Without Duplicate Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating large datasets from multiple source sheets into one master list efficiently.
Observed behavior: Instead of manually copying and pasting vast amounts of data, the user wants to implement a dynamic Power Query solution where duplicates are removed automatically, and the merged list easily updates when source data changes.

Before you start

Ensure both of your source Excel worksheets are formatted as official Excel Tables (select your data and press Ctrl+T). This ensures Power Query can accurately detect and import all your dynamic data ranges.

Solution 1Recommended

Use Power Query to Append Data and Remove Duplicates

This is the most reliable and dynamic method for merging large datasets. Appending via Power Query creates an automated pipeline that can be refreshed with a single click whenever the original data changes.

Power Query allows you to extract data from your sheets, stack them together, and apply data transformations like duplicate removal without altering your original files.

Import Your Worksheets into Power Query

Open Excel, go to the 'Data' tab, select 'Get Data', choose 'From File', and then 'From Workbook'. Select your file and import the first worksheet. Repeat this step for your second worksheet so both appear as queries.

Append the Queries

In the Power Query Editor, go to the 'Home' tab and click 'Append Queries', then choose 'Append Queries as New'. Select your two tables from the dropdown menus in the dialog box and click 'OK' to combine them into one dataset.

Remove Duplicate Records

In your newly appended query, select the specific columns that define a duplicate record (or select all columns by clicking the top-left corner of the table). Navigate to the 'Home' tab, click 'Remove Rows', and select 'Remove Duplicates'.

Load to a New Worksheet

Click 'Close & Load' in the top left corner, then select 'Close & Load To...'. Choose 'Table' and 'New worksheet' to output your combined, deduplicated data into Excel.

Use Power Query to Append Data and Remove Duplicates

Dynamic Data Updating: Whenever the data in either of your original source sheets changes, simply right-click anywhere inside your new merged table and select 'Refresh'. The list will instantly update and remove any new duplicates.

Efficient Data Management

Combine and Clean Data Quickly with WPS Spreadsheet

WPS Office provides highly intuitive built-in tools to easily consolidate worksheets and highlight or remove duplicate data, saving you from navigating complex queries when dealing with large datasets.

1. Combine Your Data: Open your files in WPS Spreadsheet and copy the data from your secondary sheets, pasting them beneath the data in your primary master sheet.
2. Select the Dataset: Highlight the entire combined dataset range that you want to evaluate for duplicates.
3. Access Data Tools: Navigate to the 'Data' tab on the top ribbon menu and click on the 'Remove Duplicates' button.
4. Execute Removal: Check the boxes for the specific columns you want to evaluate for duplicate values, then click 'OK' to instantly finalize your clean master list.

Seamless format compatibility with Microsoft Excel (.xlsx) filesBuilt-in 'Remove Duplicates' feature for instant, visual data cleaningLightweight architecture handles thousands of rows without laggingFree alternative offering professional-grade spreadsheet tools

microsoft office alternative - wps office

Frequently Asked Questions

Why use Power Query instead of simply copy-pasting the data?

Power Query creates a dynamic connection. If your source data is updated, added to, or modified, you can simply click 'Refresh' to update your merged master sheet automatically without having to manually copy, paste, and deduplicate everything all over again.

Do both Excel sheets need to have the exact same column headers?

Yes, for the 'Append Queries' feature to align your data seamlessly, it is highly recommended that both sheets share identical column names and structures. Mismatched headers will result in separate columns with null values.

Can I merge more than two sheets using this Power Query method?

Absolutely. In the 'Append Queries' dialog box, you can toggle the option from 'Two tables' to 'Three or more tables', allowing you to combine multiple sheets or even consolidate entire folders of Excel files simultaneously.

How does Power Query decide which duplicate record to keep?

By default, when you use the 'Remove Duplicates' function, Power Query preserves the very first occurrence of the duplicate row (reading from the top down) and automatically removes any subsequent identical rows based on the columns you selected.

Excel & Data Problems