How To Combine Duplicate Rows And Sum The Values In Excel?

If you enter data from multiple sources in your Excel worksheet, you are likely to enter duplicate information. These duplicate entries of data sets would need to be consolidated in order to rectify the error.

​​In a small dataset, duplicate values are easier to spot and correct; however, in a large dataset, the manual work becomes overwhelming and more prone to human error. Microsoft Excel has smart features which let you identify and sum the values of duplicate rows in a table. Read this easy tutorial to find out how to combine duplicate rows and add the values in an Excel spreadsheet.

Combining Duplicate Rows Using the Consolidate Feature

Microsoft Excel's 'consolidate' feature helps you accomplish complex tasks quickly and efficiently. With the 'Consolidate' feature, you can combine multiple spreadsheets or rows into one and also add the values.

Suppose you have a table with sales data from Indian cities. As you can see in the image below, the names of some cities are repeated. Let us consolidate the repeating values in a separate table.

Step 1: Copy the headers of the original table in the worksheet and paste it where you want the consolidated data.

We paste the headers in cells E1 and F1.

Step 2: Select the cell below the leftmost header, in this case cell E2.

Step 3: Go to the "Data" tab and select the "Consolidate" option.

Step 4: In the Consolidate dialog box, select the "Sum" option from the functions drop-down menu.

Step 5: In the "Reference" section, enter the range of cells of the table without the header and select "Left Column" under the "Use labels in" options. You can enter it manually or press shift and click the first data cell of the table and then the last data cell.

As shown in the image above, the selected range of cells will be bordered by a green dotted line.

Step 6: Click 'OK' to see the combined rows in the table with their values summed.

You will find the data values which have been repeated in the top values of the new consolidated table.

Conclusion

In conclusion, combining duplicate rows and summing the values in Excel can be a time-saving and error-reducing technique when working with large datasets. Manually identifying and rectifying duplicate entries can be overwhelming and prone to human error. However, this task becomes quick and efficient with Microsoft Excel's 'consolidate' feature. With the steps outlined in this tutorial, users can easily consolidate duplicate rows and obtain the summed values, and this feature is especially helpful for datasets that contain repetitive data.

Leverage Excel's capabilities with tutorialspoint and streamline your data analysis processes today.