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.
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:
- Select a blank cell.
- Go to the Data tab > Data Tools group > Consolidate.
The Consolidate dialog box appears. - 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.
Finally, you will get the unique rows from your initial 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:
- Select your data.
- Go to the Home tab > Editing group > Sort & Filter dropdown > Sort A to Z.
The sales values with their corresponding representatives have been arranged in the following order.
- Select your data again.
- Go to Data tab > Outline drop-down > Subtotal.
The Subtotal dialog box will appear. - 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.
The sales for each representative will be grouped and their sales values will be added up.
- Click on the minus(-) sign to collapse the Subtotal groups.
Here is our final result.
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:
- Select your data > Insert tab > PivotTable drop-down > From Table/Range.
The “PivotTable from table or range” dialog box will open up. - 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.
As 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.
- Drag down the Sales Rep to the Rows area and Sales to the Values area.
The PivotTable will appear on the left side, and you can see the combined data with their corresponding unique values.
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:
- Select the cells in the same row.
- 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:
- Select the range of cells that have duplicate values and you want to remove.
- Go to the Data tab > Data tools group > Remove duplicates.
- Select the columns where you want to identify duplicates.
- Consider your removal options, ensuring you keep the data you need.
- 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