How to Merge Duplicates in Excel (Easiest Way in 2024)

In this article, we will show you how to merge duplicates in Excel. Simply follow the steps below.

Merge Duplicates in Excel

To merge duplicates in Excel, we’ve outlined three approaches. You can choose the method that best fits the information you need.

Merge Duplicates in Excel Using “Consolidate” Feature

To achieve this, we will merge rows and columns, alter data, and arrange all of the information into a single row using the “Consolidate” tool. 

1. Scan and Identify Duplicate Entries

Scan each row carefully to identify instances where the information in certain columns is exactly the same. These identical rows represent the duplicate entries that need to be merged.

2. Access the “Data” Tab

At the top of the Excel window, locate the tabs. Click on the “Data” tab to access a variety of data-related tools and functions. This is where you’ll find the option to consolidate duplicate entries.

3. Utilize the “Consolidate” Feature

Within the “Data Tools” group, you’ll find the “Consolidate” button. Click on it to initiate the process of merging duplicate entries. This feature will help you combine duplicate rows into a single, comprehensive entry based on your specified criteria.

4. Define Consolidation Criteria

After clicking the “Consolidate” button, a dialog box will appear. In this dialog box, you’ll define how Excel should consolidate the duplicate entries. Specify the criteria such as the function to apply (e.g., sum, average) and the columns to consolidate.

5. Confirm Your Consolidation Choices

Once you’ve defined your consolidation criteria, click “OK” in the dialog box to confirm your choices. Excel will then proceed to merge the duplicate entries based on the criteria you’ve specified.

6. Review the Merged Duplicate Data

Review your data after the consolidation process. Ensure that the duplicate entries have been successfully merged into single, comprehensive entries. This step is crucial for verifying the accuracy and integrity of your dataset post-merging.

Merge Duplicates in Excel Using Advanced Filter

To merge duplicate rows and extract values to different sections of the worksheet, you can utilize the Advanced Filter tool. Simply follow the process below.

1. Access Advanced Filter After Selecting Data Ranges

After highlighting the data ranges containing the duplicate rows, navigate to the “Data” tab in the Excel ribbon. Click on “Advanced” in the “Sort & Filter” section to open the Advanced Filter options.

2. Choose your Preferred Action

In the Advanced Filter window, select an action:

• Filter the list, in-place: This filters out duplicate rows within the existing data.
• Copy to another location: This copies unique values to a separate area on the worksheet.

3. Specify the Copy Destination

If you choose “Copy to another location,” select the cell range where you want to copy the unique values.

4. Apply Advanced Filter

Ensure only distinct values are filtered or copied by checking the box labeled “Unique records only.” Click “OK” to merge the duplicate rows based on your selected action.

Merge Duplicates in Excel Using Pivot Table

If you want to merge duplicates and outline their values, use this Pivot Table method. For example, add up all the names of the sales representatives and find the total amount they have sold. 

1. Insert a PivotTable to Organize Data

After choosing the specific range of data containing merged duplicates in your Excel, navigate to the “Insert” tab in the Excel ribbon and click on “PivotTable.” This action will open a configuration window to set up your PivotTable.

2. Configure PivotTable Settings and Location

In the “PivotTable from Table/Range” window, decide whether you want to place the PivotTable in a new worksheet or an existing one. If selecting an existing worksheet, specify the location by choosing a cell reference. Click “OK” to proceed with these settings.

3. Select Columns to Include in the PivotTable Fields

On the PivotTable Fields pane, typically located on the right side, check the boxes corresponding to the columns you want to include in your PivotTable. Ensure you select all columns necessary for merging duplicate rows effectively. Then, click “Update.” 

We hope you now better understand how to merge duplicates in Excel. If you enjoy this article, you might also like our article on How to Merge Two Datasets in Excel or our articles on How to Copy Merged Cells in Excel.