How to Merge Rows in Excel without losing Data (5 Ways)

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 names of different customers in rows: 4 to 10. We want to get the names of all customers into one row. So we need to merge these rows.

Data

Download Excel Workbook

Ways to Merge Rows in Excel without Losing Data

1.  Using Clipboard

Merging rows using the Clipboard is the easiest method. First, we have to open the Clipboard from the Home Tab by clicking the downward arrow.

 Merge rows in excel without losing data using clipboard

Now we have to select the data we want to merge and then copy the data by pressing CTRL+C. The data will be shown on the Clipboard.

 merge rows in excel without losing data using clipboard

Now we need to double click on an empty cell and click on the Paste All box in the clipboard. It will merge all the data in the cell. Now you can customize your data representation as you want, for example putting commas (,) between different names.

 merge rows in excel without losing data using clipboard

2.  Using The CONCATENATE Function

We can also merge rows into a single row using the CONCATENATE function. Select an empty cell and type,

=CONCATENATE (Select the data cells one by one)

 merge rows in excel without losing data CONCATENATE

It will put all the data into a single cell.

 merge rows in excel without losing data CONCATENATE

You can put commas or space between the names by modifying the formula,

=CONCATENATE (A4,", ",A5,", ",A6,", ",A7,", ",A8,", ",A9,", ",A10)

Merge rows in excel

You can also merge multiple columns of different rows into a single row by using the CONCATENATE formula. After typing the formula you need to select the cells of a row then put a comma and select cells from another row and so on.

Merge rows in excel

3.  Using Equal Formula

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.

Merge rows in excel

After pressing ENTER, you will get all the data in the selected cell

Merge rows in excel

4.  Using Notepad

You can also merge multiple rows into a single row by using Notepad. Copy data from all the rows into an empty Notepad.

Merge rows in excel

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 customizing your data, copy it back to an empty row of your Excel file.

5. Using the TEXTJOIN Function

Select an empty cell and type,

=TEXTJOIN (" ", FALSE, select all the cells one by one)

 After selecting the cells for our dataset, the formula will look like,

=TEXTJOIN (" ", FALSE,A4,A5,A6,A7,A8,A9,A10)

After pressing ENTER, you will get all the data in the selected cell

Merge rows in excel

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. How to Merge Two Cells in Excel without Losing Any Data

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.

Prantick

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo