If you're using Excel and have data split across multiple columns that you want to combine, you don't need to manually do this. Instead, you can use a quick and easy formula to combine columns.

We're going to show you how to combine two or more columns in Excel using the ampersand symbol or the CONCAT function. We'll also offer some tips on how to format the data so that it looks exactly how you want it.

How to Combine Columns in Excel

There are two methods to combine columns in Excel: the ampersand symbol and the concatenate formula. In many cases, using the ampersand method is quicker and easier than the concatenate formula. That said, use whichever you feel most comfortable with.

1. How to Combine Excel Columns With the Ampersand Symbol

excel combined cells ampersand
  1. Click the cell where you want the combined data to go.
  2. Type =
  3. Click the first cell you want to combine.
  4. Type &
  5. Click the second cell you want to combine.
  6. Press the Enter key.

For example, if you wanted to combine cells A2 and B2, the formula would be: =A2&B2

2. How to Combine Excel Columns With the CONCAT Function

excel combined cells CONCAT
  1. Click the cell where you want the combined data to go.
  2. Type =CONCAT(
  3. Click the first cell you want to combine.
  4. Type ,
  5. Click the second cell you want to combine.
  6. Type )
  7. Press the Enter key.

For example, if you wanted to combine cell A2 and B2, the formula would be: =CONCAT(A2,B2)

This formula used to be CONCATENATE, rather than CONCAT. Using the former works to combine two columns in Excel, but it is depreciating, so you should use the latter to ensure compatibility with current and future Excel versions.

How to Combine More Than Two Excel Cells

You can combine as many cells as you want using either method. Simply repeat the formatting like so:

  • =A2&B2&C2&D2 ... etc.
  • =CONCAT(A2,B2,C2,D2) ... etc.

How to Combine the Entire Excel Column

Once you have placed the formula in one cell, you can use this to automatically populate the rest of the column. You don't need to manually type in each cell name that you want to combine.

To do this, double-click the bottom-right corner of the filled cell. Alternatively, left-click and drag the bottom-right corner of the filled cell down the column. It's an Excel AutoFill trick to build spreadsheets faster.

Tips on How to Format Combined Columns in Excel

Your combined Excel columns could contain text, numbers, dates, and more. As such, it isn't always suitable to leave the cells combined without formatting them.

To help you out, here are various tips on how to format combined cells. In our examples, we'll refer to the ampersand method, but the logic is the same for the CONCAT formula.

1. How to Put a Space Between Combined Cells

excel combined cells with space

If you had a "First name" column and a "Last name" column, you would want a space between the two cells.

To do this, the formula would be: =A2&" "&B2

This formula says to add the contents of A2, then add a space, then add the contents of B2.

It doesn't have to be a space. You can put whatever you want between the speech marks, like a comma, a dash, or any other symbol or text.

2. How to Add Additional Text Within Combined Cells

excel combined cells with additional text

The combined cells don't just have to contain their original text. You can add whatever additional information you want.

Let's say cell A2 contains someone's name (e.g., Marge Simpson) and cell B2 contains their age (e.g., 36). We can build this into a sentence that reads "The character Marge Simpson is 36 years old".

To do this, the formula would be: ="The character "&A2&" is "&B2&" years old"

The additional text is wrapped in speech marks and followed by an &. You don't need to use speech marks when referencing a cell. Remember to include where the spaces should go; so "The character " with a space at the end.

3. How to Correctly Display Numbers in Combined Cells

excel combined cell with text formula

If your original cells contain formatted numbers like dates or currency, you'll notice that the combined cell strips the formatting.

You can solve this with the TEXT function, which you can use to define the required format.

Let's say cell A2 contains someone's name (e.g., Marge Simpson) and cell B2 contains their date of birth (e.g., 01/02/1980).

To combine them, you might think to use this formula: =A2&" was born on "&B2

However, that'll output: Marge Simpson was born on 29252. That's because Excel converts the correctly formatted date of birth into a plain number.

By applying the TEXT function, you can tell Excel how you want the merged cell to be formatted. Like so: =A2&" was born on "&TEXT(B2,"dd/mm/yyyy")

That's slightly more complicated than the previous examples, so let's analyze the Excel formula step by step:

  • =A2 - merge cell A2.
  • &" was born on " - add the text "was born on" with a space on both sides.
  • &TEXT - add something with the text function.
  • (B2,"dd/mm/yyyy") - merge cell B2, and apply the format of dd/mm/yyyy to the contents of that field.

You can switch out the format for whatever the number requires. For example, $#,##0.00 would show currency with a thousand separator and two decimals, # ?/? would turn a decimal into a fraction, H:MM AM/PM would show the time, and so on.

You can find more examples and information on the Microsoft Office TEXT function support page.

How to Remove the Formula From Combined Columns

excel combined cell with formula

If you click a cell within the combined column, you'll notice that it still contains the formula (e.g., =A2&" "&B2) rather than the plain text (e.g., Marge Simpson).

This isn't a bad thing. It means that whenever the original cells (e.g., A2 and B2) are updated, the combined cell will automatically update to reflect those changes.

However, it does mean that if you delete the original cells or columns then it will break your combined cells. As such, you might want to remove the formula from the combined column and make it plain text.

To do this, right-click the header of the combined column to highlight it, then click Copy.

excel combined cell paste

Next, right-click the header of the combined column again—this time, beneath Paste Options, select Values. Now the formula is gone, leaving you with plain text cells that you can edit directly.

How to Merge Columns in Excel

excel merge and center

Instead of combining columns in Excel, you can also merge them. This will turn multiple horizontal cells into one cell. Merging cells only keeps the values from the upper-left cell and discards the rest.

To do this, select the cells or columns that you want to merge. In the Ribbon, on the Home tab, click the Merge & Center button (or use the dropdown arrow next to it for more options).

For more information on this, read our article on how to merge and unmerge cells in Excel. You can also merge entire Excel sheets and files together.

Save Time When Using Excel

Now you know how to combine columns in Excel. You can save yourself lots of time—you don't need to combine them by hand. It's just one of the many ways that you can use formulas to speed up common tasks in Excel.