Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Dataset to Merge Rows in Excel

Note: This is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

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.

Use of Merge & Center Option to Merge Rows in Excel

  • At this point, since both the rows have the element in them, Excel will trigger a warning like below.
  • Then, press OK.
Note: Here the context of our example demands the availability of only one value so we are pressing OK. If your scenario demands something different, then play accordingly.

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

Employing Merge Cells Feature to Merge Rows in Excel

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

Using Format Cells Feature to Merge Rows in Excel

  • 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.
=CONCATENATE(B5,", ",C5)

Applying CONCATENATE Funcion to Merge Rows in Excel

Formula Breakdown

  • CONCATENATE(B5,”, “,C5) → The CONCATENATE function adds two or more text strings together.
  • B5 and C5 are text strings.
    • Output: The Silent Patient, Alex Michaelides.
  • Explanation: the CONCATENATE function adds the Book The Silent Patient with the Author Alex Michaelides.
  • 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.

Using Clipboard to Merge Rows in Excel

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

Using TRANSPOSE Function to Merge Rows in ExcelSteps:

  • First of all, we will type the following formula in cell B16.
=CONCATENATE(@TRANSPOSE(D5))

Formula Breakdown

  • TRANSPOSE(D5) → the TRANSPOSE function makes the merged cell D5 into an unmerged one.
  • CONCATENATE(@TRANSPOSE(D5)) the CONCATENATE function adds the unmerged cell D5 in cell B16.
    • Output: Alex Michaelides
  • Explanation: Here, Alex Michaelides is the unmerged author name 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.
=CONCATENATE(C5,", ",C6)
  • 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.

Applying CONCATENATE and TRANSPOSE Functions to Merge 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

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo