You can also choose other options such as Count or Average or Max/Min. I chose to get the SUM of the values from each record. In the end result, you get a unique list of countries along with the sales value from the original data set. The above steps would consolidate the data by removing the duplicate entries and adding the values for each country. Select the range A2:B9 (the data excluding the headers).Click on the range selection icon in the Reference field.In the Consolidate dialog box, select Sum from the function drop-down (if not already selected by default).In the Data Tools group, click on the Consolidate icon.

Select the cell below the leftmost header.

Copy the headers of the original data and paste it where you want the consolidated data.

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. Suppose you have a data set as shown below where the country name repeats multiple times. The other method is to use a Pivot table and summarize the data (covered next in this tutorial). 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.

Combine and Sum Data Using Pivot TablesĬombine and Sum Data Using the Consolidate Option.

Combine and Sum Data Using the Consolidate Option.

Move merge duplicate rows in excel