Excel: How to Combine Rows with Same ID

You can use the Consolidate function in the Data tab in Excel to combine rows with the same ID.

The following example shows how to use this function in practice.

Example: How to Combine Rows with Same ID in Excel

Suppose we have the following dataset in Excel that contains information about sales made by various employees at some company:

Notice that several of the rows have the same value in the ID column.

Suppose we would like to combine the rows with the same ID values and simply sum the values in the Sales column.

To do so, we can make sure cell E1 is active and then click the Data tab along the top ribbon and then click the icon called Consolidate within the Data Tools group:

In the new window that appears, choose Sum as the function, then type A1:C11 as the range.

Then check the boxes next to Use labels in Top Row and Left column:

Once you click OK, the rows with the same ID values will be combined and the values in the Sales column will be summed:

The new dataset combines the rows from the original dataset with the same ID values and calculates the sum of sales for each ID.

For example, the employee with the ID value of A001 had individual sales values of 7, 5 and 3.

The sum of these values is 7 + 5 +3 = 15.

The same calculation is performed for each unique ID value.

Note that you can also consolidate the data using a different summary statistic (e.g. count, average, max, etc.) by choosing a different value from the dropdown menu for Function within the Consolidate window.