How to combine multiple Excel columns into one?

Are you having difficulty merging two or more Excel columns? Knowing how to combine multiple columns in Excel without losing data is a handy time-saver that allows you to consolidate your data and make your sheet look neater.

First and foremost, you should know that there are multiple ways you can merge data from two or more columns in Excel. Excel’s Merge & Center button keeps only the value in the upper-left cell and deletes the rest, so to combine columns without losing data you need one of the methods below.

If the columns you need live in different sheets or files, Sheetgo can merge them for you, in Excel and Google Sheets alike, and keep the merged file updated automatically. Its AI Processor can also fix values that don’t match between files, such as a country written three different ways, before the data lands in your master file.

How to combine multiple cells or columns in Excel without losing data?

Whether you’re learning how to combine two cells in Excel, or ten, one of the main benefits of merging is that the formulae don’t change. Here are the following ways you can combine cells or merge columns within your Excel:

Use ampersand (&) to merge two cells in Excel

If you want to know how to merge two cells in Excel, here’s the quickest and easiest way of doing so without losing any of your data.

1. Double-click the cell in which you want to put the combined data and type =

2. Click a cell you want to combine, type &, and click the other cell you wish to combine. If you want to include more cells, type &, and click on another cell you wish to merge, etc.

3. Press Enter when you have selected all the cells you want to combine

While this is useful for quickly merging data into a single cell, the merged data will not be formatted. This can make data untidy or challenging to read in some instances (e.g. full names or addresses).

If you want to add punctuation or spaces (delimiters), follow the below steps. For this example, let’s put a comma and a space between the first and last name as you would see on a registration list:

1. Double-click the cell in which you want to put the merged data and type =

2. Click a cell you want to merge

3. This time, type &", "& before you click the next cell you want to merge. If you want to include more cells, type &", "& before clicking the next cell you want to merge, etc.

4. Press Enter when you have selected all the cells you want to combine

As you can see, now your merged data comes out in a neater format, with each piece of data appropriately separated.

Use the CONCATENATE function to merge multiple columns in Excel

This method is similar to the ampersand method, and also allows you to format your merged data. First, you need to use the CONCATENATE function to merge a row of cells:

1. Insert the =CONCATENATE function as laid out in the instructions above

2. Type in the references of the cells you want to combine, separating each reference with ,”, “, (e.g. B2,”, “,C2,”, “,D2). This will put a comma and a space between each value.

3. Press Enter

Now that you have successfully merged your cells, you can follow these simple steps to merge multiple columns:

1. Hover your mouse over the bottom-right corner of the merged cell you just created

2. When the cursor changes into a + symbol, drag your cursor as far down the column as you want and release it

Once you release the mouse, you should see that your merged cell has become a merged column, containing all of the data from your chosen columns.

*The CONCAT function is the newer replacement for CONCATENATE in Excel 2019 and later. It works the same way but also accepts whole ranges, such as =CONCAT(B2:D2), although it has no separate delimiter argument like TEXTJOIN.

Use the TEXTJOIN function to merge multiple columns in Excel

This method works in Excel 2019 and later, including Microsoft 365 and Excel for the web. As you can probably tell, this function is helpful when you want to combine two or more text cells in Excel.

The following steps will show you how to use the TEXTJOIN function, once again using the comma and space combination to create your first merged cell:

1. Double-click the cell in which you want to put the combined data

2. Type =TEXTJOIN to insert the function

3. Type (“, “,TRUE, followed by the references of the cells you want to combine, separating each reference with a comma (the role of TRUE is to disregard empty cells you may have input)

4. Press Enter

In order to create the rest of your combined column, use the drag-and-drop steps listed below:

1. Hover your mouse over the bottom-right corner of the merged cell you just created

2. When the cursor changes into a + symbol, drag your cursor as far down the column as you want and release it.

Now your columns of data have successfully merged into your new column.

Use the INDEX formula to stack multiple columns into one column in Excel

Let’s say you want to create a stack of data from your multiple columns, rather than create a single cell. You can easily do this across multiple cells and columns within your spreadsheet using the INDEX formula:

1. Select all of the cells containing your data

2. Type in a name for this group of data in the “Name Box” (box located to the left side of the formula bar). In this example, I’ve named the data “_my_data”

3. Select an empty cell in your Excel sheet where you want your stacked data to be located. Input the following INDEX formula (remember to substitute with your data name):

=INDEX(_my_data,1+INT((ROW(A1)-1)/COLUMNS(_my_data)),MOD(ROW(A1)-1+COLUMNS(_my_data),COLUMNS(_my_data))+1)

4. The first value from your data range should appear. Hover over the cell until the cursor changes into a + symbol, and drag your cursor as far down until you receive a #REF! value (this signals the end of your data set)

Read Microsoft’s INDEX function page to learn more about the different components of the INDEX formula.

Other ways to combine multiple columns in Excel: Notepad and VBA

There are two other ways you can combine multiple columns in Excel. These are often more time-consuming, and use other tools as part of the process. However, they may be more helpful for users who wish to avoid using Excel formulae.

Use Notepad to merge multiple columns in Excel

You can use Notepad to extract, format, and replace your data from multiple columns in your Excel. For this, you need to copy and paste each column from your Excel sheet into a Notepad file. Then, use the Replace function to add commas between each value. Once finished, you can copy and paste your formatted data back into your Excel.

Use VBA script to combine two or more columns in Excel

As an alternative to the INDEX function stacking method, you can use VBA script. Right-click the sheet tab and select “View Code”, paste a macro that copies each column into the destination column, and press “F5” to run it.

Conclusion

As you can see, combining multiple columns is easy in Excel. Whether you’re combining multiple Excel files, or columns and cells, there are a variety of ways that cater to different users, depending on their technical abilities or needs.

As a result, not only can you format your Excel into a cohesive and seamless spreadsheet, but also save time and optimize your productivity when evaluating, managing, or sharing important data. Once you know how to combine multiple columns in Excel into one column, combining or merging your data can become one quick and simple task.

Working in Google Sheets instead? See how to use CONCATENATE in Google Sheets. And if you need to go the other way, here’s how to split cells in Excel.