Editor choice

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.

data set

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.

merge and center

There will pop up a warning window.

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 applying merge and center command

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. 

after applying merge and center command to entire province row

2) Using Clipboard [Keeping Data Intact]

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

selection of two rows

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

 applying paste

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.

clipboard opening

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) 

applying command sequence

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

clipboard method

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

final outcome in clipboard method

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.

inserting space and comma between merged texts

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

=CONCATENATE(A2," ",A3)

inserting space between merged tests

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.

concatenate and transpose method

 

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

converting reference into values

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

display spaced text in merged cell

Feel free to apply the process to the entire dataset.

final merged 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.

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

ExcelDemy
Logo