How to Merge Duplicate Rows in Excel (3 Methods)

Method 1 – Using the Consolidate Command

Steps:

  • Select a blank cell.
  • Go to the Data tab > Data Tools group > Consolidate.
    Going to the Consolidate commandThe Consolidate dialog box appears.
  • In the Consolidate dialog box:
    • Select Sum in the Function drop-down. Or any other option useful for your task.
    • Choose the range in the Reference box. Your selected range will appear inside the “All references” box.
    • Check the Left column and click OK.

    Selecting range and function in the consolidate dialog box

You will get the unique rows from your initial data.

Image after consolidate data

Read More: How to Combine Duplicate Rows in Excel without Losing Data


Method 2 – Applying the Subtotal Feature

Steps:

  • Select your data.
  • Go to the Home tab > Editing group > Sort & Filter dropdown > Sort A to Z.
    Applying Sort & Filter featureThe sales values with their corresponding representatives have been arranged in the following order.
    Sorted cell range
  • Select your data again.
  • Go to Data tab > Outline drop-down > Subtotal.
    Applying the Subtotal feature The Subtotal dialog box will appear.
  • In the Subtotal dialog box:
    • Select the first column name in the “At each change in” field.
    • Select Sum in the “Use function” field.
    • Select the 2nd column name from the “Add subtotal to” menu.
    • Mark the “Replace current subtotals” and Summary below data” checkboxes.
    • Press OK.

    Choices selection in Subtotal dialog box The sales for each representative will be grouped, and their sales values will be added up.Grouped data after applying Subtotal feature

  • Click on the minus(-) sign to collapse the Subtotal groups.
    Collapsing the data

Here is the final result.

Image after collapsing the data


Method 3 – Using the Pivot Table Feature

Steps:

  • Select your data > Insert tab > PivotTable drop-down > From Table/Range.
    Inserting PivotTable to the datasetThe PivotTable from table or range” dialog box will open up.
  • From the PivotTable from table or range” dialog box:
    • Click on the New Worksheet button. Or, select the Existing Worksheet.
    • Press OK.

    Selecting range from the "PivotTable from table or range" dialog boxYou will be taken to a new sheet, where the PivotTable and PivotTable Fields will appear on the left and right sides.
    PivotTable appears on the new sheet

  • Drag down the Sales Rep to the Rows area and Sales to the Values area.
    Dragging PivitTable fields

The PivotTable will appear on the left side, and you can see the combined data with their corresponding unique values.

Final output using PivotTable

Read More: Combine Duplicate Rows and Sum the Values in Excel


Download the Practice Workbook


Related Articles


<< Go Back to Merge Duplicates in Excel | Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo