3 Ways to Merge Duplicate Rows in Excel

Sometimes we need to merge duplicate rows in Excel. In this article, we are going to show you how to merge duplicate rows in Excel with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

📂  Download Excel File

Download free workbook from here:

Learn to Merge Duplicate Rows in Excel with 3 Approaches

While writing this article, “Merge Duplicate Rows in Excel ”, we’ve considered a dataset that contains approximately 3 columns and 15 rows. In this dataset, we added Name, and Points to make you better understand. But if you want, you can change the entities and put the values of your own.

One of the easiest ways to merge duplicate rows in Excel is using the Consolidate option. It is effortless and simple. You can do this just by following the given steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell E6.
• Secondly, go to the Data tab.
• Thirdly, choose the Consolidate option.

• Now you will get a dialogue box named Consolidate.
• Select Sum at Function.
• Assign the Reference as:

• Select Left column as Use labels in.
• Finally, click OK.

• Consequently, you will get the result.

Undoubtedly, this is a very simple technique in Excel.

📕 Read More: 3 Ways to Convert Multiple Rows to Single Column in Excel

Another simple process is applying Excel Pivot Table. You can do it easily. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select cell B6.
• Secondly, click on the Insert tab.
• Now select PivotTable and then From Table/Range.

• Now you will get a dialogue box named PivotTable from table or range.
• Thirdly, select the Table/Range and put the following value.

• Select Existing Worksheet.
• Fourthly, select Location and write the given value.

• Now press OK.

• Now you will get PivotTable1.

• Now click wherever you want on the PivotTable1.

• On the right side of the worksheet, you will get PivotTable Fields.
• Fifthly, select NAMES, and Points.
• Now select Name at Rows and Sum of Points at Values.

• Eventually, you will get the required results.

Lastly, using this technique is very unique in Excel.

📕 Read More: 4 Quick Ways to Merge Rows with Same Value in Excel

Removing duplicates and using the SUMIF function to merge rows in Excel is a very simple method. In fact, it is one of the easiest methods. You can do it by maintaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the entire E column.
• Secondly, select the Data tab.
• Thirdly, click on Remove Duplicates.

• Now you will get a dialogue box named Remove Duplicates.
• Select My data has headers.
• Select Name from Columns.
• Fourthly, click OK.

• Now you will see that all the duplicate names have been removed.

• Fifthly, select cell F6.
• Now write the following formula down on that cell.

• Sixthly, tap Enter.

• Consequently, you will see the desired result.

Truly, this is a very handy method in Excel. You can also get this by searching: Combine Duplicate Rows and Sum the Values in Excel.

📕 Read More: 3 Ways to Combine Duplicate Rows and Sum Values in Excel

How to Merge Duplicate Rows in Excel Without Losing Data

If you want to merge duplicate rows in Excel without losing data then this part is for you. You can achieve this by following the given steps using the CONCATENATE function.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell C17.
• Secondly, write the following formula down on the cell.

• Now press Enter.
• Finally, you will get the results you required.

Undoubtedly, this is a very easy method in Excel.

📄 Important Notes

While performing the processes mentioned above, you need to be aware of these things:

🖊️  You need to be careful while using the Consolidate option.

🖊️  While applying the Excel Pivot Table, you need to be conscious about selecting the cell.

🖊️  You should be careful while using the Excel functions.

📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Firstly, we’ve used the Consolidate option to merge duplicate rows in Excel.

📌  Secondly, we adopted Excel Pivot Table.

📌  Thirdly, we applied the Removing Duplicates option and used the SUMIF function.

📌  Finally, we’ve tried to show you how to merge duplicate rows in Excel without losing data.

Conclusion

We wish that you would be able to merge duplicate rows in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.

Related Articles

• 6 Ways to Combine Multiple Rows in One Cell in Excel
• 3 Suitable Ways to Combine Rows in Excel with Same ID
• 6 Suitable Ways to Convert Multiple Rows to Single Row in Excel