Don’t forget to share this post

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.

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
How to Combine Multiple Excel Columns Into One Screenshot 1

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
How to Combine Multiple Excel Columns Into One Screenshot 2

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
How to Combine Multiple Excel Columns Into One Screenshot 3

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
How to Combine Multiple Excel Columns Into One Screenshot 4

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.

How to Combine Multiple Excel Files Into One
How to Combine Multiple Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE

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
How to Combine Multiple Excel Columns Into One Screenshot 5

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.
How to Combine Multiple Excel Columns Into One Screenshot 6

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

The Beginner's Guide to Excel Version Control

Discover what Excel version control is, the version control features Excel has to offer, and how to use them to share, merge, and review Excel changes

READ MORE
The Beginners Guide to Excel Version Control

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.

How to combine Excel data with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

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
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 10 2022, Updated May 30 2022