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

In this article, I am going to discuss the available methods offered by Excel to merge two consecutive rows into one single row. I use Merge & center, Clipboard, CONCATENATE function, a formula combining CONCATENATE & TRANSPOSE function for two different outputs; Losing Data & Intact Data.

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 complex dataset.

Dataset for practice

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

1) Using Merge & Center Method (it will lose your data) 

Now, I want the name of the Provinces row to be merged into one row.

To do that, go to the Home tab >Alignment section > Merge & Center group of commands > Merge & Center.

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.

We can see that inside the Active Cell only the Text from the Upper Active Cell (A3) is present. Excel has kept the upper value only.

For the rest of the rows you can apply the Merge & Center command. 

2) Using Clipboard [Keeping Data Intact]

Normally in Excel when we select two rows and Copy(Ctrl+C).

Then Paste(Ctrl+V) it to another cell. We’ll see the rows will not be merged.

However, the Excel Clipboard feature does the work. Let’s explore how that works.

Step 1. In the HOME tab > Clipboard section > click  Icon. Clipboard Window will appear on the left side of the workbook.

Step 2. 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) 

For better understanding, I repeat the command sequence for rows B3,B4 again.

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

3) Using the CONCATENATE Function [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.

By putting [“, ”] as a delimiter you can place Space & Comma between two rows’ elements.

=CONCATENATE(A2,", ",A3)

Space & Comma will appear between row texts shown as in the image.

If you want to place a Space between the texts then use [“ ”] as your delimiter.

=CONCATENATE(A2," ",A3)

In various situations, if we need to deal with multiple or numerous data CONCATENATE function can be used as follows.

=CONCATENATE(B2,", ",B3,", ",B4,", ",B5,", ",B6,", ",B7,", ",B9,", ……)

4) Using CONCATENATE & TRANSPOSE functions [Keeping Data Intact]

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

You have to highlight the TRANSPOSE part of the formula & it converts cell reference into cell values.

=CONCATENATE(TRANSPOSE(A1:A10&” “))

Step 1. Inside a cell where you want to merge the two rows enter the formula & then select the TRANSPOSE part as shown below.

Step 2. Press F9. It will convert the reference into values.

Step 3. Remove the Curly Braces {} & enter desirable space in the value inside quotation delimiter.

Feel free to apply the process to the entire dataset.

Though merging the two rows of the entire dataset is not necessary, I show it for explaining the process.

Then you can see the two rows merged into one with space between them & without losing any data.

Conclusion

In the article, we focused on merging two rows. Various situations demand different approaches in order to achieve the desired solution. Hope 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.