Disclaimer: This post includes affiliate links

If you click on a link and make a purchase, I may receive a commission at no extra cost to you.

Mastering Data Management in Excel: Techniques for Combining Multiple Columns Efficiently

What to Know

• Using the concatenate formula in Microsoft Excel you can combine two or more columns of data into one without losing any data.
• Once you’ve created the CONCATENATE formula in the first cell,drag the Fill Handle to duplicate the formula for remaining cells.
• Once combined, you need to change the merged data to values using copy and paste so you can delete or change the original data.

This article explains how to combine two columns of data in Microsoft Excel into a single column without losing that data.

Dean Pugh / Unsplash

Click here

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 theCONCATENATE 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 selectInsert from the menu that appears.
2. If your other columns have headers, give the new column a header name. In our example, it’sFull Name .
   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)
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.
   
4. Once you’ve completed the formula, pressEnter on your keyboard to activate it. The new data combination should appear in the cell.

Click here

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 theFill 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 shortcutCtrl + C on Windows orCommand + C on Mac to copy it.

7. Then, in the first corresponding cell of the column you copied the data from, right click and selectPaste 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.

Was this page helpful?

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Subscribe

Tell us why!

Other Not enough details Hard to understand

Submit

Also read: