How to Merge Rows with the Same Value in Excel (6 Methods)

When working with large Excel datasets, you often encounter multiple rows that share the same value in one or more columns. Merging these rows effectively can help you summarize, clean, or analyze your data more efficiently. Excel doesn’t have a single built-in “merge rows” button, but there are several powerful methods you can use to combine rows with matching values, depending on your goals.

In this article, you’ll learn multiple ways to merge rows with the same value in Excel. We’ll cover manual approaches, formulas, Power Query, and VBA automation, so you can pick the method that best suits your needs.

This is a quick and simple method to make your Excel sheet cleaner and more readable. It doesn’t perform any mathematical operation or data consolidation, but it aligns repeated values visually.

This is the dataset we will be using:

Steps:

➤ Right-click your data and go to Custom Sort under Sort drop-down.

➤ Sort your data by the ID column and select Smallest to Largest as your Order. Then, click OK.

➤ Select the repeated cells under the ID column such as A2:A3.
➤ Go to the Home tab >> Click “Merge & Center” from the Alignment group.

➤ Repeat for each group of duplicate IDs. You can additionally merge another column such as Product for a cleaner look.

Excel’s Consolidate tool is perfect for when you need to merge and summarize numeric values like sales totals or quantities across rows that share the same label in a specific column.

Steps:

➤ Go to a new sheet and click into cell A1.
➤ Go to the Data tab >> Click Consolidate under Data Tools.

➤ Choose “Sum” as the function.
➤ Click “Add” and select the data range from the original sheet such as A1:C6 using the small arrow button at the right.
➤ Check “Top row” and “Left column” if your data has headers.
➤ Click OK.

You’ll see a new summary table with unique IDs and total sales.

If your goal is to combine product names or categories across rows with the same ID without affecting your numeric data like Sales, TEXTJOIN function is a clean and formula-based solution. This method works best when you extract a unique list of IDs and then display merged product names per ID.

Steps:

➤ In a new sheet or adjacent section, create a unique list of IDs in column D. If you have Excel 365, use this formula in E2:

➤ In F2, enter this formula to merge product names for each ID:

➤ Press  Ctrl  +  Shift  +  Enter  if your Excel version requires array entry. In Excel 365, just press Enter.

➤ In G2, sum the sales for each ID using:

This gives you a consolidated view by ID with all associated products in one cell and total sales calculated.

The Subtotal feature is ideal when you want to add up numeric values for grouped rows manually. It provides intermediate summaries and is easy to collapse or expand for detailed views.

Steps:

➤ Sort your dataset by the ID column.

➤ Go to the Data tab >> Subtotal under the Outline group.

➤ Set At each change in to ID, Use function to Sum and Add subtotal to Sales.
➤ Click OK.

➤ Excel will insert subtotal rows below each group.

➤ Click on the small 2 button to collapse rows and view your summary.
➤ Select the empty range in Product column.

➤ Go to Home tab >> Click on Editing group >> Choose Go to Special under Find & Select.

➤ Select blanks and click OK.

➤ Without clicking anywhere else, type =B2 using your keyboard and hit  Ctrl  +  Enter  for the results to spill automatically.

Now you have a condensed list with merged numeric values.

Power Query is the best tool when working with large datasets or ongoing reports. It allows grouping rows by a value (e.g., ID) and performing operations like summing or joining.

Steps:

➤ Go to the Data tab >> From Table/Range under Get & Transform group.

➤ Check your headers and click OK to load your data into Power Query Editor.

➤ Go to Home tab >> Group By.

➤ Group by ID and name your column >> Set Operation to Sum and Column to Sales. Then, click OK.

➤ Click Close & Load under Home tab to return results to Excel.

You now have a dynamic table that merges rows by ID.

If your dataset is large or repetitive, VBA provides a way to programmatically combine rows that share the same value. It’s useful for applying the same logic repeatedly with one click.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Insert a new Module from Insert tab >> Module and paste this code into the blank area:

Sub MergeRowsWithSameID()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Sort by ID (Column A)
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("A2:A" & lastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:C" & lastRow)
        .Header = xlYes
        .Apply
    End With
    ' Loop from bottom up and merge rows
    For i = lastRow To 2 Step -1
        If ws.Cells(i, 1).Value = ws.Cells(i - 1, 1).Value Then
            ws.Cells(i - 1, 3).Value = ws.Cells(i - 1, 3).Value + ws.Cells(i, 3).Value
            ws.Rows(i).Delete
        End If
    Next i
End Sub

➤ Press  F5  key to run the macro.

This code sums the “Sales” column for duplicate IDs and removes the extra rows.

Frequently Asked Questions

Can I merge rows with the same value without losing any data?

Yes, by using functions like TEXTJOIN, Power Query, or VBA, you can merge rows while preserving all relevant data, including both text entries and numerical totals.

What’s the best method for large datasets?

Power Query and VBA are ideal for large datasets. They efficiently process thousands of rows, reduce manual work, and ensure your workbook remains fast, organized, and easy to update.

Is there a built-in tool in Excel to merge duplicates?

Yes, Excel’s Consolidate feature allows you to merge duplicates by summarizing numeric values using functions like SUM or AVERAGE. However, it doesn’t support merging or combining text fields.

Can I undo Merge & Center easily?

Yes. Just select the merged cell(s), go to the Home tab, and click “Merge & Center” again. This will instantly unmerge the cell and restore individual cell structure.

Wrapping Up

In this tutorial, we explored multiple ways to merge rows with the same value in Excel from visual merging to advanced formulas, built-in tools, automation using Power Query and VBA. Each method fits different use cases depending on your data size, format, and whether you want to combine numbers, text, or both. Feel free to download the practice file and share your feedback.