How to Combine Two Columns in Excel

Merge columns without losing their data

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 the 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 outlines how to merge two columns of data in Excel into a single column without losing that data.

How to Combine Columns in Excel Without Losing Data

If you 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 you're trying to merge the data from two columns into a single column, the merge command won't work. Instead, you'll 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 nearby; this is where your combined data will go.

    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 first cell below the heading of the new column (C2 in this example) enter the following into the formula bar:

    =CONCATENATE(A2," ",B2)

    This function 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 you can use any other separator you like.

    For example, if there's a comma between the quotation marks =CONCATENATE(A2,","B2), then the data from cell A will separate from the data in cell B by a comma.

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

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

    The results of the CONCATENATE formula in Microsoft Excel.
  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 back 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.

    Doing this will apply the formula to all selected rows.

    An illustration of how to use the Fill Handle in Microsoft Excel.
  6. If you delete any of the formula data (in this example, from columns A or B), the combined data in column C will 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.

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

    The Past Value option in Microsoft Excel.
  8. The combined data will paste 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?