How to Merge Rows Without Losing Data in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Merge Rows in Excel Without Losing Data


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.

Use Clipboard to Merge Rows in Excel Without Losing Data

  • 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.

 Merge Rows Applying Excel CONCATENATE Function Without Losing Data

Here, in the function, we inserted each cell as text values.
  • 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

Insert Ampersand Operator to Merge Rows in Excel Without Losing Data

  • However, you can put commas or spaces between the names by modifying the formula.
=B5&","&B6&","&B7&","&B8&","&B9&","&B10&","&B11

Merge Rows in Excel Without Losing Data

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.

Use Notepad to Merge Rows Without Losing Data in Excel

  • 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.

Apply Excel TEXTJOIN Function to Merge Rows Without Losing Data

In the TEXTJOIN function, we inserted blank as delimiter, FALSE as ignore_type and all the cell values as text.

Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1 Comment
  1. Thank You! This helps a lot!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo