Editor choice

How to Merge Two Rows in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am going to discuss the available methods offered by Excel to merge two consecutive rows into one single row. Suppose, I have a dataset like this, where I have 4 columns, that contains the net profit of a couple of products in different provinces.

Using this dataset, I will show you different ways to merge two rows.

Note that this is a concise dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

This article will demonstrate to merge two rows in Excel by using the Merge & center command, using the Clipboard feature, applying the CONCATENATE function, and nesting CONCATENATE TRANSPOSE functions for two different outputs; Losing Data & Intact Data.

Method 1: Using Merge & Center Command to Merge Two Rows in Excel (It Will Lose Your Data)

Here, I want the name of the Provinces row to be merged into one row by using the Merge & Center feature.

Steps:

  • Firstly,, go to the Home tab
  • Secondly,  Merge & Center the group of commands under the Alignment group.
  • Thirdly, click on Merge & Center command.

Using Merge & Center Command to Merge Two Rows in Excel

  • So, There will pop up a warning window.
  • If you explicitly know losing the data may not hamper you then click OK. For the time being, we are clicking OK.

  • After that, you will see the merged text named New York.
  • Then, for the rest of the rows, you can apply the Merge & Center command.

  • Finally, you will the output in the below image.

Read More: How to Combine Multiple Rows into One Cell in Excel


Method 2: Applying Excel Clipboard Feature to Merge Two Rows (Keeping Data Intact)

Here, we will use the Clipboard feature to merge two rows in Excel. Let’s explore how that works.

Steps:

  • In the HOME tab > Clipboard section > click  Icon. The clipboard Window will appear on the left side of the workbook.

Applying Clipboard Feature to Merge Two Rows in Excel

  • Then, select the two Rows > press Ctrl+C (copy) > Select Any Cell >Double Click On it > Click on the available Item to Paste. (Command Sequence)

  • Therefore for further application of the command sequence for the rest of the rows, the outcome will be as in the below image.


Similar Readings


Method 3: Applying CONCATENATE Function to Merge Two Rows in Excel (Keeping Data Intact)

The CONCATENATE function or Concatenation Operator is used to merge two or more rows using various delimiter types. Delimiters are used to separate the different texts.

Steps:

  • Firstly, select the C13 cell.
  • Then, write down the following formula here.
= CONCATENATE(C5,"  ",C6)
  • So, Space & Comma will appear between row texts shown as in the image.
  • Besides, if you want to place a Space between the texts then use [“ ”] as your delimiter.
  • After that, press Enter.

Using the CONCATENATE Function to Merge Two Rows in Excel

  • Therefore, you will get the result in cell C13.
  • Besides, use the Fill Handle tool and drag it down from cell C13 to cell C15.
  • Similarly, apply the same procedure for the 14th and 15th rows.

  • Lastly, you will get all the results here.

Using the CONCATENATE Function to Merge Two Rows in Excel

Read More: How to Merge Rows with Comma in Excel


Method 4: Nesting CONCATENATE & TRANSPOSE Functions to Merge Two Rows (Keeping Data Intact)

A combination of the CONCATENATE & TRANSPOSE functions can be used to merge two rows’ of values without losing any data.

Steps:

  • Firstly, select the C13 cell.
  • Then, write down the following formula here.
=CONCATENATE(TRANSPOSE(B5)," ", B6)
  • After that, press Enter.

Nesting CONCATENATE & TRANSPOSE functions to Merge Two Rows in Excel

  • Therefore, you will get the result in cell C13.
  • Besides, use the Fill Handle tool and drag it down from cell C13 to cell C15.
  • Again, apply the same procedure for the 14th and 15th rows.

  • As a result, you can see the two rows merged into one with space between them & without losing any data.

Nesting CONCATENATE & TRANSPOSE functions to Merge Two Rows in Excel

Read More: How to Convert Multiple Rows to Single Row in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In the article, we focused on merging two rows. Various situations demand different approaches in order to achieve the desired solution. Hope the methods mentioned in the article clear your concept & fasten your excel uses. I encourage you to comment & share this article if you benefited from it.


Related Articles

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo