Merge and Combine Columns without Losing Data in Excel

When merging columns in Excel, you may find that using the "Merge and Center" command only keeps the upper-left cell's content and discards the rest. If you want to combine all data from multiple columns without losing any information, this guide provides three practical methods—ranging from simple to highly efficient professional solutions.

• Merge Columns into One Cell Using Clipboard
• Quickly Merge Columns Without Losing Data Using Kutools
• Merge Columns into One Column Using Formulas
  • Using Ampersand (&)
  • Using CONCATENATE function

Merge Columns into One Cell Using Clipboard

If you simply want to merge multiple columns' data into one single cell, the Clipboard provides a quick and easy method.

1. Under the Home tab, click the "Clipboard launcher" (small arrow) in the bottom-right of the "Clipboard" group to enable the Clipboard.

   

2. Select the columns you want to merge, and press "Ctrl" + "C" keys or click "Home" > "Copy "to copy them.
3. Double click into a target cell. In the Clipboard, click the copied item.

   

All values will now be inserted into the cell as a continuous text block.

Quickly merge columns of data without losing data with Kutools for Excel

For a professional, flexible, and accurate merging process, "Kutools for Excel" offers the "Combine Rows, Columns, or Cells Without Losing Data" feature. It allows you to merge with custom separators, choose output location, and preserve original formatting.

After installing Kutools for Excel, select the columns you want to merge. Go to the "Kutools" tab, select "Merge & Split" > "Combine Rows, Columns or Cells without Losing Data". Then do as follows:

1. Choose "Combine Columns" in the first section.
2. Specify a separator (e.g., Space)
3. Choose where to place the result.
4. Decide how to handle merged cells (keep, clear, or merge).
5. Click "OK".
   

Your data will be merged with full formatting preservation - dates, percentages, and numbers remain intact.

Merge Columns into One Column Using Formulas

If you prefer formula-based merging, Excel provides basic functions like Ampersand (&) and CONCATENATE, though you should be aware they do not preserve formatting.

Merge columns of data using the Ampersand (&)

Select a blank cell, enter either of the below formulas and press "Enter". Drag down the fill handle to apply the formula to other rows.

Merge columns without separator:

=A2&B2&C2

Merge columns with space (replace the separator with any other separator you need):

=A2&" "&B2&" "&C2

Result

Columns are merged into a single column without separator:

Columns are merged into a single column with spaces:

Merge columns of data into one column using CONCATENATE

Here, I will introduce the CONCATENATE function as a more efficient formula to merge columns.

1. In a blank cell, type the following formula:

   =CONCATENATE(A2:C2&",")

   

2. Highlight A2:C2&"," and press F9 to evaluate. This step will change the cell references to values.

   

3. Remove {} brackets manually.

   

4. Press "Enter" to finalize the result.

   

Demo: Combine Multiple Columns without Losing Data in Excel using Kutools