How to Combine Two Columns in Excel

How to Combine Columns in Excel Without Losing Data

If you just want to merge two empty columns in Excel, that's easy enough to do using the Merge option, but if those columns contain data, you'll lose all the data except what's in the uppermost left cell. If what you're actually trying to do is merge the data from two columns into a single column, the merge command won't work. Instead, you need to use the CONCATENATE formula to combine that data.

1. In the Excel worksheet where you want to combine two columns of data, first insert a new column near the data you want to combine. This is where your combined data will be displayed.

   To insert a new column, right click a column to the right of where you want the new column to appear and select Insert from the menu that appears.

2. If your other columns have headers, give the new column a header name. In our example, it's Full Name.

3. Select the the first cell below the heading of the new column (C2 in this example) enter the following into the formula bar:

   =CONCATENATE(A2," ",B2)

   This tells excel you want to combine the data in cell A2 with the data in cell B2, with a space (" ") between them. In this example, the space between quotation marks is the separator, but if you choose, you can use any other separator you like.

   For example, if a comma were between the quotation marks, like this: =CONCATENATE(A2,","B2) then the data from cell A would be separated from the data in cell B by a comma.

   You can use this same formula to combine the data from several columns. You just need to write it using the same syntax as above: =CONCATENATE (Cell1, "Separator", Cell2,"Separator", Cell 3...etc)

   

4. Once you've completed the formula, press Enter on your keyboard to activate it. The new data combination should appear in the cell.

   

5. Now, you can copy the formula down the length of the column to combine all the desired entries. To do that, place your cursor bac in the previous cell (C2 in the example), grab the green dot (called the Fill Handle) in the lower right corner of the screen and drag down the length of the column you want to use.

   This will apply the formula to all selected rows.

   

6. Now, the data in the new column is part of a formula, and as such, if you delete any of the data used in the formula (in this example, any data in columns A or B) it will cause the combined data in column C to disappear.

   To prevent this, you need to save all the new entries as a value so they won't disappear. So first, highlight all the combined data you just created and use the keyboard shortcut Ctrl + C on Windows or Command + C on Mac to copy it.

   

7. Then, in the first corresponding cell of the column you copied the data from, right click and select Paste Value.

   

8. The combined data will be pasted into the column as a value and you can change or delete the data from the original columns without changing the new, combined data.