You can add borders for the last two rows. Similarly, we have done this job for other sales representatives also. So, we can see that the duplicate rows of the person Bill has been merged here. Click on the minus(-) sign beside Bill Total to minimize the two different sales values achieved by this person.Later, the sales for each representative will be grouped and their sales values will be added up. Click on the Replace current subtotals and Summary below data options and press OK.Select the following column names and options in their assigned fields.Then, you will have the Subtotal dialog box. Select the data range, then go to the Data tab > Outline group > Subtotal.Later, the sales values with their corresponding representatives have been arranged in the following order. Select the range, and then go to the Home tab > Sort & Filter dropdown > Custom Sort.

Here, we will use the Subtotal feature to combine the sales values of the duplicate sales representatives.įirst, we need to sort the names in order so that the duplicate names can stay together. Read More: How to Merge Rows in Excel (4 Methods). In the end, you will get the unique list of the sales representatives along with their total sales from your initial data set.

Check the Left column option and press OK.

Then, our selected range will appear inside the All references box.

From the Function drop-down, select Sum (or any option you find useful for your task), and choose the range in the Reference.

Go to the Data tab > Data Tools group > Consolidate.Īfterward, the Consolidate wizard will open up.

Select the first cell of the range where you want the output.

To gather the consolidated data we created a table on the right side.

We will see step by step how this tool helps us in solving our problems. It helps you to summarize your information from your data table from its different locations. The Consolidate option is used to combine information from multiple rows, worksheets, or workbooks into one place. Method-1: Using Consolidate Option to Merge Duplicate Rows in Excel We will show 5 widely used methods for merging duplicate rows to consolidate our sample data.

We need to merge the data set so that each sales representative is recorded only once in the table together with his total sales. Let’s assume, we have a data set containing sales data of several sales representatives in an Excel worksheet.

Merge duplicate rows in excel 2007