How to Merge Rows in Excel (5 Easy Ways)

In Excel, situations may arise when you need to merge cells (rows and columns). Today we are going to show you 5 easy methods to merge rows in Excel.

Practice Workbook

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

5 Easy Ways to Merge Rows in Excel

The following dataset has the SI. No, Book and Author columns. Here 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 5 easy methods. Here, we used Excel 365. You can use any available Excel version.

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.

I. 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.

II. 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.

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.

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.

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 Author name and Book name in a different cell.

Steps:

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

• After that, press ENTER.
• Therefore, you can see the result in cell D5.
• Furthermore, we will drag down the formula with the Fill Handle tool.

Hence, the rows have been merged, and you can see the Book and Author Name in a similar row.

4. 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 is helpful to merge a number of 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.

5. Combine CONCATENATE & TRANSPOSE Functions

In this method, we will use the combination of 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.

Afterward, we will use the CONCATENATE function to add the Books of the author in cell C16.

Steps:

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

• Afterward, 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.

Practice Section

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

Conclusion

That’s all for today. We have listed 5 methods to merge rows in Excel. 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.

Further Readings

• How to Concatenate in Excel (3 Suitable Ways)
• Concatenate Two Columns In Excel (5 Methods)
• How to Concatenate with Space in Excel (3 Suitable Ways)
• Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
• How to Combine Rows into One Cell in Excel