Merge and Combine Rows without Losing Data in Excel

Excel only keeps the data in the upper-left most cell, if you apply "Merge & Center" command (Home tab > Merge & Center on the Alignment panel) to merge rows of data in Excel. Users have to use another method to merge multiple rows of data into one row without deleting data. This tutorial is going to present you the method of how to merge rows of data into one row. The following method is appliable for Excel 2000, 2002/XP, 2003, 2007, 2010 and later versions.

• Method A: merge rows of data into one row with formula (type the formula > apply the formula to other rows > Copy and paste the merged data).
• Method B: quickly merge rows of data into one row with Combine feature of Kutools for Excel.

Method A: merge rows of data into one row with formula.

To merge row A 1, A 2, A3 into A 4, we would type the following formula in A4:

=A1&A2&A3

If we want to add spaces between A1, A2 and A3, we would type the following formula in A4:

=A1&" "&A2&" "&A3

Suppose we need to merge A1, A2 and A3 in the following figure.

Step 1. Enter formula  =A1&A2&A3 into the A4 cell.

Step 2. Apply the formula to other rows.

To do this, select the first merged cell and then hover the mouse over the bottom right corner of the cell until you see the plus sign. Drag the cursor forwards until the selection includes all the remaining cells in the merged row. When you release the mouse, the merged row should contain the dat in the first, second and third rows merged.

Step 3. Copy these merged data and paste to any location you want it to be (click the "123", or "paste special" > "Values").

Step 4. Delete the rows (1-3) which have been merged. It is OK now.

Method B: quickly merge rows of data into one row with Combine feature of Kutools for Excel.

It is much easier to merge rows of data into one row with the third party add-in Kutools for Excel.

Step 1. Select the rows you want to merged, and apply Combine feature from Kutools > Combine on the Ranges and Cells panel.

Step 2. Specify the options for combining rows, and click "OK".

The following figure is the result after clicking "OK".

Kutools for Excel

More than 100 Advanced Functions for Excel 2007 and 2010