Merge Rows in Excel (Using Functions, Features and VBA)

This article will cover many essential methods to merge rows in Excel. We’ll walk you through applying features and VBA for merging rows. You will also learn to use functions for merging rows without losing any existing data.

Suppose you have a dataset with duplicate information. You can merge similar values to avoid redundancy. Joining rows is useful for consolidating the same data, combining text, and analysis.

After going through the context, you must be able to save time and effort to avoid redundancy. So, let’s get started.

How to Merge Rows in Excel

The following dataset has the SI. No, Book and Author columns. In our example dataset, you can see the authors’ names are repetitive. We will merge the rows in Excel containing the same author name. To do so, we will go through several methods. Here, we used Microsoft 365. You can use any available Excel version.

Method 1: Utilize Excel Alignment Group to Merge Rows

Excel provides merge options itself for merging the rows. You will find the options in the Alignment section from the Home tab. Let’s merge rows using these options.

1.1 Use Merge & Center Option

Among the merge options, Merge & Center is used frequently. And here, we will merge rows in Excel using the Merge & Center option.

Steps:

• In the beginning, we will select the cells to be merged.
• Here, we select cells D5:D6.
• After that, from the Home tab >> go to the Merge & Center group.
• At this point, several options will appear.
• Then, select the Merge & Center option.

• At this point, since both the rows have the element in them, Excel will trigger a warning like below.
• Then, press OK.

• Therefore, you can see that cells D5:D6 are merged cells.

• In a similar way, we merged other cells that have similar Author names.
• Hence, you can see the result in the following picture.

1.2 Employ Merge Cells Option

In this method, we will use the Merge Cells option to merge rows in Excel.

Steps:

• In the first place, we will select the cells to be merged.
• Here, we select cells D5:D6.
• After that, from the Home tab >> go to the Merge & Center group.
• At this point, several options will appear.
• Then, select the Merge Cells option.

• At this moment, it will again show us a warning since the rows have values.
• Then, click OK.

• As a result, you can see that cells D5:D6 are merged cells.

• In a similar way, we merged other cells that have similar Author names.
• Hence, you can see the result in the following picture.

You may wonder why we have skipped the Merge Across option, it’s because Merge Across only merges cells from columns.

Method 2: Use Format Cell Feature to Integrate Rows

In the earlier section, we have seen different merge options to merge rows in Excel. There is an alternative way of merging rows. Here, we will use the Format Cells feature to do the task. You can also merge two rows in Excel using the Format Cells feature.

Steps:

• First of all, select the cells you want to merge together.
• Here, we selected cells D5:D6.
• After that, we will press CTRL+1 keys to bring out the Format Cells dialog box.

• At this moment, a Format Cells dialog box will appear.
• Then, from the Alignment group >> mark Merge Cells.
• Furthermore, click OK.

• At this moment, it will again show us a warning since the rows have values.
• Then, click OK.

• Hence, you can see that cells D5:D6 are merged cells.

• In a similar way, we merged other cells that have similar Author names.
• Therefore, you can see the result in the following picture.

Method 3: Apply CONCATENATE Function to Unite Rows in Excel

In this method, we will use the CONCATENATE function to merge rows in Excel. Here, using this function, we will merge book names for Alex Michaelides.

Steps:

• First of all, we will type the following formula in cell C16.

• After that, press ENTER.
• Therefore, you can see the result in cell C16.

Method 4: Use CONCAT Function to Merge Rows in Excel

Choose the C16 cell >> insert the following formula >> press Enter.

Method 5: Use Ampersand Symbol to Merges Rows

Select the C16 cell >> insert the following formula >> hit Enter.

Method 6: Use TEXTJOIN Function to Merge Rows in Excel

Select cell C16 >> insert the below formula >> hit Enter.

Method 7: Use Clipboard Category to Merge Rows by Keeping Data Intact

In this method, we will use the Clipboard feature to merge rows in Excel. This method is helpful when you want to merge rows without losing data. Thus, this method helps merge several rows together in a cell.

Here, in the following picture, you can see that author Alex Michaelides has two books in cells C5 and C6. We will put the two books together in cell C16.

Steps:

• Firstly, we will select cells C5:C6.
• After that, to copy these selected cells >> press CTRL+C.

• Then, go to the Home tab.
• Then, from the Clipboard group >> click on the Clipboard icon, which is marked with a red color box.

• At this moment, you can see the copied Book names are present on the Clipboard.
• Also, you can notice that the copied Book names are merged together in the Clipboard.
• Then, we will double-click on cell C16 since we want to paste the copied value in that cell.
• Along with that, we will click on the Book name that is presented on the Clipboard.

• Therefore, you can see that the copied Book names are presented in a single cell.
• Hence, we can merge rows by keeping values intact.

Method 8: Combine CONCATENATE & TRANSPOSE Functions

In this method, we will combine the CONCATENATE and TRANSPOSE functions to merge rows in Excel.

Here, in the following picture, you can see the merged Author name.

Further, using the CONCATENATE and TRANSPOSE functions, we will insert an Author name in cell B16.

Afterwards, we will use the CONCATENATE function to add the author’s books in cell C16.

Steps:

• First of all, we will type the following formula in cell B16.

• Afterwards, press ENTER.
• Therefore, you can see the result in cell B16.

• Moreover, we will merge the Books name of author Alex Mechaelides in cell C16.
• To do so, we will type the following formula in cell C16.

• The CONCATENATE function adds two or more text strings together.

• After that, press ENTER.
• Hence, you can see the result in cell C16.
• Therefore, we have merged rows in Excel.

Method 9: Merge Rows in Excel Using Notepad

Select range C10:C13 >> press Ctrl+C.

Open the Notepad editor >> press Ctrl+V >> format the copied text.

Now, press Ctrl+A to select >> press Ctrl+C to copy.

Choose cell C16 >> go to the formula bar >> press Ctrl+V.

Method 10: Run Excel VBA Code to Merge Rows

Choose the intended sheet >> press Alt+F11 to open the VBA editor.

Now, navigate to Insert >> click on Module >> paste the following code >> press F5.

Sub MergeSimilarRows()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
   
    Set ws = ThisWorkbook.Worksheets("Excel VBA")
   
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
   
    Application.DisplayAlerts = False
   
    For i = lastRow To 5 Step -1
        If ws.Range("D" & i).Value = ws.Range("D" & i - 1).Value Then
            ws.Range("D" & i - 1 & ":D" & i).Merge
        End If
    Next i
   
    Application.DisplayAlerts = False
    ws.Columns("D").AutoFit

End Sub

As a result, we will see an output like the below one.

Things to Remember

• Using built-in features like Merge & Center and Format Cells may lose existing data.
• You must use the mentioned formula and functions when merging multiple rows without losing existing data.
• If you want to run the Excel VBA code for other datasets, you must modify the code accordingly.

Frequently Asked Questions

• How do I combine multiple rows into one in Excel?

You can use the CONCATENATE function to combine multiple rows into one in Excel. We can also use the ampersand (&) operator. When merging rows, we must remember that the data is in consecutive rows.

• What is the shortcut key for merge rows in Excel?

There are several shortcuts to access the Merge & Center feature without using the ribbon. To apply Merge & Center, press Alt+H+M+C. If you want to unmerge, hit the Alt+H+M+U keys.

• How do I merge columns in Excel?

To merge columns in Excel, we use the CONCATENATE or TEXTJOIN functions. You can also use the ampersand (&) symbol. Enter the formula in an empty cell, like =A1&B1&C1 for columns A, B, and C.

Practice Section

You can download the above Excel file to practice the explained method.

Download Practice Workbook

You are welcome to download the practice workbook from the link below.

Conclusion

That’s all for today. We have listed several essential methods to merge rows in Excel. I hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here. Please visit our website Exceldemy to explore more.

Merge Rows in Excel: Knowledge Hub

<< Go Back to Merge in Excel | Learn Excel