3 Ways to Merge Duplicate Rows in Excel

Get FREE Advanced Excel Exercises with Solutions!

Merging Duplicate rows in Excel means combining duplicate entries of your Excel data for better presentation and organization. It ensures a clean dataset, avoids double-counting, enables aggregated reporting, facilitates database integration, and enhances analysis accuracy.

In this Excel tutorial, you will learn to merge duplicate rows in Excel by using 3 Excel features.

Suppose you have some sales data in which several sellers appear multiple times, as shown in the following image. For instance, Michael shows up twice with his separate sales figures. Ideally, you’d prefer Michael to be listed just once with his total sales combined.

Dataset to merge duplicate rows in excel

Here are the 3 methods to merge duplicate rows in Excel:


Using the Consolidate Command

The Consolidate option in Excel combines data from multiple sources, such as rows, worksheets, or workbooks, into one location. This allows you to create a consolidated summary of information.

To merge duplicate rows in Excel using the Consolidate command, follow the steps below:

  1. Select a blank cell.
  2. Go to the Data tab > Data Tools group > Consolidate.
    Going to the Consolidate commandThe Consolidate dialog box appears.
  3. In the Consolidate dialog box:
    • Select Sum in the Function drop-down. Or any other option useful for your task.
    • Choose the range in the Reference box.
      Then, your selected range will appear inside the “All references” box.
    • Check the Left column and click OK.

    Selecting range and function in the consolidate dialog box

Finally, you will get the unique rows from your initial data.

Image after consolidate data

Read More: How to Combine Duplicate Rows in Excel without Losing Data


Applying the Subtotal Feature

The Subtotal feature is a tool that helps you aggregate and consolidate data in a list or a table. It’s important to note that the Subtotal feature doesn’t involve merging rows in the traditional sense. Instead, it inserts rows with subtotal calculations, creating a structured outline of your data.

To merge duplicate rows in Excel using the Subtotal feature, follow the steps below:

  1. Select your data.
  2. Go to the Home tab > Editing group > Sort & Filter dropdown > Sort A to Z.
    Applying Sort & Filter featureThe sales values with their corresponding representatives have been arranged in the following order.
    Sorted cell range
  3. Select your data again.
  4. Go to Data tab > Outline drop-down > Subtotal.
    Applying the Subtotal feature The Subtotal dialog box will appear.
  5. In the Subtotal dialog box:
    • Select the first column name in the “At each change in” field.
    • Select Sum in the “Use function” field.
    • Select the 2nd column name from the “Add subtotal to” menu.
    • Mark the “Replace current subtotals” and Summary below data” checkboxes.
    • Press OK.

    Choices selection in Subtotal dialog box The sales for each representative will be grouped and their sales values will be added up.Grouped data after applying Subtotal feature

  6. Click on the minus(-) sign to collapse the Subtotal groups.
    Collapsing the data

Here is our final result.

Image after collapsing the data


Using the Pivot Table Feature

The Pivot Table feature in Excel is a powerful tool for summarizing and analyzing large data. While the PivotTable feature is not directly used for merging rows, it enables you to achieve similar results by grouping, summarizing, and restructuring your data.

To merge duplicate rows in Excel using the PivotTable feature, follow the steps:

  1. Select your data > Insert tab > PivotTable drop-down > From Table/Range.
    Inserting PivotTable to the datasetThe PivotTable from table or range” dialog box will open up.
  2. From the PivotTable from table or range” dialog box:
    • Click on the New Worksheet button. Or, select the Existing Worksheet, it’s your choice.
    • Press OK.

    Selecting range from the "PivotTable from table or range" dialog boxAs a result, you will be taken to a new sheet where on the left side the PivotTable and on the right side the PivotTable Fields will appear.
    PivotTable appears on the new sheet

  3. Drag down the Sales Rep to the Rows area and Sales to the Values area.
    Dragging PivitTable fields

The PivotTable will appear on the left side, and you can see the combined data with their corresponding unique values.

Final output using PivotTable

Read More: Combine Duplicate Rows and Sum the Values in Excel


Download Practice Workbook


Conclusion

In this article, we have discussed three different ways to merge duplicate rows in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Frequently Asked Questions

How do I merge the same row in Excel?

To merge the same row in Excel:

  1. Select the cells in the same row.
  2. Go to the Home tab > Alignment group > Merge & Center.

Thus, your cells in the same row are merged.

How do I remove duplicates in Excel?

To remove duplicates in Excel:

  1. Select the range of cells that have duplicate values and you want to remove.
  2. Go to the Data tab > Data tools group > Remove duplicates.
  3. Select the columns where you want to identify duplicates.
  4. Consider your removal options, ensuring you keep the data you need.
  5. Click OK.

Your duplicate values are removed.

How can I identify duplicate rows in Excel before merging them?

To identify duplicate rows, you can use the Conditional Formatting feature. Highlight the relevant columns, go to Home, click on Conditional Formatting, and choose Duplicate Values.


Related Articles


<< Go Back to Merge Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo