How to Combine Duplicate Rows and Sum the Values in Excel

As a part of my full-time job a few years ago, one of the things I had to deal with was to combine data from different workbooks shared by other people.

And one of the common tasks was to combine the data in such a way that there were no duplicate records.

For example, below is a dataset that multiple records for the same region.

Dataset with duplicate records

And the final result needs to be a consolidated dataset where each country is reported only once.

Dataset where duplicate rows are combines and sum

In this tutorial, I will show you how to combine duplicate rows and sum the values to create a single consolidated dataset.

Combine and Sum Data Using the Consolidate Option

If all you need to do is consolidate data and add all the values for the repeating records, it’s best to use the consolidate feature in Excel.

The other method is to use a Pivot table and summarize the data (covered next in this tutorial).

Suppose you have a data set as shown below where the country name repeats multiple times.

Dataset with duplicate records

While these are unique records as the sales value is different, for reporting purposes you may want to remove multiple instances of the same country and show the sales value as one consolidated sum.

Below are the steps to do this:

  1. Copy the headers of the original data and paste it where you want the consolidated dataCopy the headers
  2. Select the cell below the leftmost headerSelect the left most cell below the header
  3. Click the Data tabClick the Data tab
  4. In the Data Tools group, click on the Consolidate iconClick the Consolidate icon
  5. In the Consolidate dialog box, select Sum from the function drop-down (if not already selected by default)Select Sum as the function
  6. Click on the range selection icon in the Reference field.Select the Left Column option
  7. Select the range A2:B9 (the data excluding the headers)
  8. Select the Left column checkboxSelect the Left Column option
  9. Click Ok

The above steps would consolidate the data by removing the duplicate entries and adding the values for each country.

Dataset where duplicate rows are combines and sum

In the end result, you get a unique list of countries along with the sales value from the original data set.

I chose to get the SUM of the values from each record. You can also choose other options such as Count or Average or Max/Min.

In this example, I have shown you how to consolidate the data in a single data set in a worksheet. you can also use this feature to consolidate data from multiple worksheets in the same workbook, and even multiple different workbooks.

Combine and Sum Data Using Pivot Tables

A Pivot Table is the Swiss army knife of slicing and dicing data in Excel.

It can easily give you a summary which is a combined data set with no duplicates and the values that are the sum of all the similar records, and do a lot more.

The downside of this method as compared to the previous one is that this one takes more clicks and a few more seconds compared to the previous one.

Suppose you have a data set as shown below where the country name repeats multiple times and you want to consolidate this data.Dataset with duplicate records

Below are the steps to create a Pivot table:

  1. Select any cell in the dataset
  2. Click the Insert tabClick the Insert Option in the ribbon
  3. In the Tables group, click on PivotTable optionClick the Pivot Table option
  4. In the Create PivotTable dialog box, make sure the Table/Range is correctMkae sure the Pivot Table range is correct
  5. Click on the Existing WorksheetChoose the Existing worksheet option
  6. Select the location where you want the resulting Pivot Table to be inserted.Add the cell reference where you want the resulting pivot table
  7. Click OK

The above steps would insert a Pivot table in the selected destination cell.

Pivot Table inserted

Now, we can do all sorts of things with a pivot table – including consolidating our data set and removing the duplicates.

Below are the steps to do this:

  1. Click anywhere in the pivot table area and it would open the pivot table pane on the right
  2. Drag the put the Country field into the Row area
  3. Drag and put the Sales field into the Values area

Add fileds to the row and value areas

The above steps summarize the data and give you the sum of sales for all the countries.

Resulting summarized data

If this is all that you want and you have no need for a pivot table, you can copy the data and paste it as values somewhere else and delete the pivot table.

This will also help you reduce the size of your Excel workbook.

So these are two quick and easy methods that you can use to consolidate the data where it would combine the duplicate rows and sum all the values in those records.

I hope you found this tutorial useful!

You may also like the following Excel tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment