Combine Multiple Columns in Excel into One Column | Layer Blog (2024)

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. Before we get started exploring these different ways, let’s start with a key step that helps the process - how to merge cells in Excel.

If you want to combine Google Sheets data, you can do that easily using Layer. Layer is a free add-on that allows you to share sheets or ranges of your main spreadsheet with different people. On top of that, you get to monitor and approve edits and changes made to the shared files before they're merged back into your master file, giving you more control over your data.

Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

How to Combine Multiple Cells or Columns in Excel Without Losing Data?

Once you have merging cells under your belt, learning how to combine multiple Excel columns into one column becomes intuitive.

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. 1. Double-click the cell in which you want to put the combined data and type =
2. 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. 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. 1. Double-click the cell in which you want to put the merged data and type =
2. 2. Click a cell you want to merge
3. 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. 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. 1.Insert the =CONCATENATE function as laid out in the instructions above
2. 2.Type in the references of the cells you want to combine, separating each reference with ,", ", (e.g. B2,", ",C2,", ",D2). This will create spaces between each value.
3. 3. Press Enter

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

1. 1.Hover your mouse over the bottom-right corner of the merged cell you just created
2. 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 another formula used for combining data from different cells. However, it is limited to two references and does not allow you to include delimiters.

Use the TEXTJOIN function to merge multiple columns in Excel

This method works only with Excel 365, 2021, and 2019. 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. 1. Double-click the cell in which you want to put the combined data
2. 2. Type =TEXTJOIN to insert the function
3. 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. 4. Press Enter

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

1. 1. Hover your mouse over the bottom-right corner of the merged cell you just created
2. 2. When the cursor changes into a + symbol, drag your cursor as far down the column as you want and release it.

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. 1. Select all of the cells containing your data
2. 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. 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)

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)

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

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. Simply right-click and select “View code” within your Excel, and copy and paste the code in a new window. Press “F5” to run the code and create a Macro. You can then apply this to your Excel by selecting your data range and applying it to your destination column.

Conclusion

As you can see, combining multiple columns is easy in Excel. Whether you’re combing 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.

Hady ElHady