Merging different rows into one is one of the most common tasks performed in Excel. You can merge different rows into one by using the “Merge and Center” command. But in this method, the merged row will only show the upper left row’s data and you will lose the data of all other rows. In this article, I will discuss several techniques by which you will be able to merge rows in Excel without losing any data.
Look at the following Dataset. Here we have the names of different customers in rows: 5 to 11. We want to get the names of all customers into one row. So we need to merge these rows.
We can do this by following the 5 ways given below.
1. Using Clipboard to Merge Rows Without Losing Data in Excel
Merging rows using the Clipboard is the easiest method. Here are the steps to do that.
Steps:
- Firstly, open the Clipboard from the Home tab by clicking the downward arrow shown below.
- Now, select the data we want to merge.
- Then, copy the data by pressing CTRL + C.
- Thus, the data will be shown on the Clipboard.
- Next, we need to double-click on an empty cell.
- After that, click on the Paste All box in the clipboard.
- Finally, it will merge all the data in the cell.
- Additionally, you can customize your data representation as you want, for example putting commas (,) between different names.
Read more: How to Merge Rows and Columns in Excel
2. Joining Rows Without Losing Data by Applying Excel CONCATENATE Function
We can also merge rows into a single row using the CONCATENATE function.
Steps:
- In the beginning, select an empty cell and insert the following formula.
=CONCATENATE(B5,B6,B7,B8,B9,B10,B11)- Then, it will put all the data into a single cell.
- However, you can put commas or spaces between the names by modifying the formula.
=CONCATENATE(B5,",",B6,",",B7,",",B8,",",B9,",",B10,",",B11)
Read more: How to Merge Rows in Excel Based on Criteria
3. Inserting Ampersand Operator to Merge Rows in Excel
You can merge rows without losing data by inserting the Ampersand Operator in the formula. To do that, follow the steps given below.
Steps:
- To start with, select an empty cell and press =, Then select the first cell, type &, select the second cell, again type & select the third cell, and so on.
- After that, pressing ENTER, you will get all the data in the selected cell.
- Here, we used the following formula.
=B5&B6&B7&B8&B9&B10&B11
- However, you can put commas or spaces between the names by modifying the formula.
=B5&","&B6&","&B7&","&B8&","&B9&","&B10&","&B11
Read More: How to Combine Multiple Rows into One Cell in Excel
4. Using Notepad to Combine Rows Without Losing Data in Excel
You can also merge multiple rows into a single row by using Notepad.
Steps:
- Firstly, select data from all the rows and press CTRL + C.
- Then, paste the data into an empty Notepad.
- Now, you can customize your data in the notepad easily.
- For example, you can put all the names in one line, or you can insert a comma between two names.
- After that, copy it back to an empty row of your Excel file.
5. Applying Excel TEXTJOIN Function to Merge Rows
In the final method, we will show how you can use the TEXTJOIN function to merge rows in Excel.
Steps:
- In the beginning, select an empty cell and type the following formula.
=TEXTJOIN(" ",FALSE,B5,B6,B7,B8,B9,B10,B11)- After that, press ENTER and you will get all the data in the selected cell.
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Download Practice Workbook
You can download the workbook to practice yourself.
Conclusion
By using any of the methods you can easily merge different rows into one without losing any data in Excel. If you want to merge two cells or merge two columns, you can take a look at the following article. Feel free to leave a comment if you have any confusion or if you know any other method for merging rows that we might have missed.
Thank You! This helps a lot!