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, from the Alignment group > go to Merge & Center.
- Thirdly, click on Merge & Center command.
- 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.
- 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.
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.
- 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.
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.
- 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.
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 benefit from it.
Related Articles
- How to Merge Rows Based on Criteria in Excel
- Excel Combine Rows with Same ID
- Convert Multiple Rows to A Single Column in Excel
- How to Merge Rows and Columns in Excel
- How to Merge Rows with Same Value in Excel
<< Go Back to Merge Rows in Excel | Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!