How to merge sheets into one and remove the duplicates in Excel?
In many practical situations, Excel users often work with large workbooks containing multiple sheets that share the same structure. These sheets may also include duplicate records, either due to repetitive data entry, data consolidation from various sources, or periodic updates. When you need to consolidate all this information efficiently—such as creating a summary report, organizing historical records, or preparing a clean dataset for analysis—the challenge arises: how to merge these sheets into one master sheet and eliminate any duplicate entries in a quick and reliable way?
This article covers several practical approaches to achieve this in Excel, ranging from simple manual operations to more advanced automated methods, each suitable for different scenarios and user needs. Below are the solutions you'll find, along with clickable links for quick navigation:
Merge sheets into one and remove duplicates with Copy and Paste
Merge sheets into one and remove duplicates with Kutools for Excel’s Combine function
Merge and remove duplicates automatically using VBA
Merge and remove duplicates using Power Query (Get & Transform)
Merge sheets into one and remove duplicates with Copy and Paste
Excel does not include a built-in feature to instantly merge multiple sheets and eliminate duplicates, but this can be accomplished through manual steps that are straightforward and accessible to all users. If dealing with a small number of sheets or when automation is not required, manual copy and paste is a practical choice. However, for many sheets or frequent updates, the process can be time-consuming and error-prone.
1. Open the first sheet (e.g., Sheet1), select its data range, and press Ctrl + C to copy. Switch to a new blank sheet (such as Sheet_Merged), select the starting cell (A1 or your preferred cell), and press Ctrl + V to paste the data.
2. Repeat the copying and pasting step for each additional sheet you want to consolidate, pasting each below the previous block of data in the merged sheet. Take care to avoid leaving blank rows between datasets, as this can interfere with removing duplicates later.
3. After all desired data is gathered in the merged sheet, select the entire range. Then, navigate to the Data tab and click Remove Duplicates.
4. In the Remove Duplicates dialog, check or uncheck My data has headers as appropriate. If headers are included only once at the top, check the option; if multiple header rows are present in your selection, uncheck it to avoid missing data. Review which columns are selected for duplicate detection to ensure accuracy.
5. Click OK. Excel will process and inform you how many duplicate values were removed and how many unique values remain. This summary can help validate the results.
6. Click OK to close the dialog. The merged sheet now contains the consolidated data from all selected sheets, with duplicates eliminated.
This manual solution is best used for occasional tasks with a manageable number of sheets. Its main advantage is simplicity and universal applicability, requiring no additional tools or permissions. However, it's less suitable for complex or recurring consolidation needs, as manual operations can become tedious and introduce human errors. If your workbook contains a high volume of data or if you need to repeat the process regularly, consider utilizing more automated options as described below.
Merge sheets into one and remove duplicates with Kutools for Excel’s Combine function
When you have a large number of sheets—such as dozens or even hundreds—to be consolidated, performing manual copy and paste is impractical. In these scenarios, using add-ins like Kutools for Excel can significantly streamline your workflow. The Combine function in Kutools enables users to quickly unify multiple sheets into one, minimizing user input and avoiding common manual mistakes. This solution is especially suitable if you frequently need to combine data, or if you manage workbooks with a consistently structured format.
1. Click Kutools Plus > Combine within Excel. In the Combine Worksheets dialog, choose the option Combine multiple worksheets from workbooks into one worksheet.
2. Press Next. In the next window, click Add > File or Folder as needed, and locate the workbook(s) containing sheets you want to consolidate.
3. Ensure the correct workbooks are checked in Workbook list, and in Worksheet list select all sheets to be included into the merged sheet.
4. Click Next. In this step, designate how many title (header) rows each original sheet contains in the Title row number field. For typical tables with a single header, enter1 so that only the first row of each sheet is treated as a header and others are considered as data.
5. Click Finish. Specify the output location and the name for the new, merged workbook where consolidated data will be saved.
6. Press Save. After combining, a prompt asks whether to open the new file.
7. Choose Yes to view the results. Your selected data from multiple sheets has been brought together into one.
8. To remove duplicates in this merged sheet, highlight the combined data, then go to Kutools > Select > Select Duplicate & Unique Cells.
9. In the Select Duplicate & Unique Cells dialog, mark Duplicates (Except1st one) and Select entire rows for a comprehensive selection of duplicate records.
10. Click OK (twice if prompted). All duplicate rows will be selected.
11. Right-click on any of the selected row numbers, and select Delete from the context menu. Deleting these rows removes duplicated data from your merged results.
Now, you have a master sheet containing data from multiple sources, standardized and deduplicated.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Pros: Highly efficient for a large number of sheets, automation of repetitive steps, and extended options for advanced merging needs. The interface guides users through each stage, reducing errors. Cons: This method requires the Kutools add-in, which may not be available in all work environments.
If any issues arise during the combine process, such as missing data or mismatched columns, check that all sheets use the same column structure and header format. If duplicate removal does not select all intended rows, verify that the correct columns are checked.
Merge two tables into one with duplicates removed and new data updated by Kutools for Excel’s Tables Merge
If your scenario involves maintaining or updating a master table with new records from another table—such as monthly sales merges, ongoing project lists, or periodic survey results—the Tables Merge utility in Kutools for Excel provides a streamlined approach. It not only merges data but can also update existing entries and append new, unmatched rows, with options for duplicate management. This tool is ideal for users regularly updating databases or reconciliating lists, where precision and up-to-date information are required.
1. Highlight the main table you wish to keep and update (for example, the master record), and click Kutools Plus > Tables Merge.
2. In the Tables Merge dialog, select the secondary table in the Select the lookup table field.
3. Click Next. In the next step, tick the column(s) (key field) that serves as the unique identifier for matching records between both tables.
4. Click Next. Select the columns you want to update in the master table when new data is found in the secondary table. You can check all columns or just those you want refreshed.
5. Click Next. If there are no new columns to add, skip the step. In the final step, under Add options tick Add mismatched rows to the end of the main table to ensure all new data appears in your master list. Under Update options, tick Only update the cells when there is data in the lookup table to avoid overwriting with blanks.
6. Click Finish. The two tables will be merged into the main table. Existing records are updated with new values or information, and any unmatched rows from the lookup table are appended to the end.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Advantages: Supports both updating and appending data, provides flexibility in how duplicates are managed, and is well-suited for ongoing data maintenance tasks. Note: Careful key column selection is important to correctly match and merge records. Double-check the preview before merging, especially if using partial matches.
Merge and remove duplicates using Power Query (Get & Transform)
Power Query (also called Get & Transform in some Excel versions) offers a flexible and dynamic way to merge data from multiple sheets and clean up duplicates—all without code or manual copy/paste. Power Query lets you import data from multiple sources, append tables, apply transformations, and easily refresh when the underlying data changes. This solution is especially useful for those who work with evolving datasets, want a refreshable consolidation, or prefer managing data flows through an easy-to-use interface rather than writing macros.
Steps to merge multiple sheets and remove duplicates with Power Query:
1. Go to the Data tab and choose Get Data > From Other Sources > Blank Query, or use From Table/Range if starting with existing data. In the Power Query Editor, you will create queries for each target sheet:
2. If you used Blank Query, proceed as follows:
- In the Power Query Editor, select Home > Advanced Editor to open the M language editor.
- Delete the default code (which usually looks like let ... in ...).
- Paste the following M code into the editor:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content] in Source
- Make sure you have converted your range to Table.
- Replace
Table1with the appropriate table name or defined range for each sheet in your workbook. Repeat to make a query for each sheet you want to consolidate.
3. After loading each sheet as a query, select them in Power Query Editor and click Append under Query tab to combine them into one.
4. An Append dialog box pops up, specify both of the Queries you have just created, and then click OK.
5. With the combined query highlighted, go to Remove Rows > Remove Duplicates, which eliminates repeated records based on all columns by default.
6. Click Close & Load to output the merged and deduplicated data to a new worksheet.
You now have a clean, consolidated table that can be refreshed automatically whenever data changes in any of the original sheets. Power Query makes updating your master data set effortless—simply right-click the result table and choose Refresh.
Advantages: Efficient for dynamic data, easy to maintain, does not require add-ins or code, and supports complex transformations. Limitations: Initial setup may be less straightforward for absolute beginners, and advanced adjustments may require some Power Query (M language) skills. If you have named ranges or tables, Power Query will show them by name. For best results, ensure your data structures (headers/columns) match across sheets. If some sheets have extra columns, unify columns before appending and removing duplicates, or explicitly select only relevant columns in each query.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in