What merging columns actually does in Excel

Excel does not have a single "merge columns" button that combines data the way you might expect. When you use Excel's merge cells feature, it combines the cell boxes themselves into one larger box — but it only keeps the text from the top-left cell and deletes everything else. That is almost never what you want when you have data in multiple columns.

What you actually need is a formula that pulls text from separate columns and joins it together in a new column, keeping all the original data intact. This is called concatenation, and it takes about 30 seconds to set up. Your original columns stay exactly as they are, and you get a new column with the combined result.

Key Takeaways

  • Excel's merge cells feature deletes data — use a formula instead to combine text from multiple columns without losing anything.
  • The CONCATENATE function or the ampersand (&) symbol both join text from different cells into one cell.
  • Create your combined column in an empty column next to your data, then copy the formula down for every row.
  • If you want to remove the original columns after combining, copy the combined column and paste it as values first so the formulas do not break.

Using the ampersand method (fastest approach)

The ampersand symbol (&) is the quickest way to join columns. Click on an empty cell where you want the combined result to appear — usually the column right after your last data column. Type an equals sign, then the cell reference for your first column, then &, then the cell reference for your second column.

For example, if your first name is in column A and your last name is in column B, click on cell C1 and type: =A1&B1. Press Enter. The two names will appear together in C1. If you want a space between them, use: =A1&" "&B1. The space goes inside the quotation marks.

Now copy this formula down to every row with data. Click on C1, then drag the small square at the bottom-right corner of the cell down to the last row. Excel will automatically adjust the cell references for each row (A2&B2, A3&B3, and so on).

Using CONCATENATE if you prefer a function name

CONCATENATE does exactly the same thing as the ampersand method, but it uses a function name instead of a symbol. Click on your empty cell and type: =CONCATENATE(A1,B1). Press Enter, then copy the formula down to all your rows the same way.

If you want spaces or other text between the columns, put them in quotation marks inside the parentheses: =CONCATENATE(A1," ",B1). You can combine three or more columns this way too: =CONCATENATE(A1," ",B1," ",C1).

The ampersand method and CONCATENATE produce identical results. Use whichever one feels more natural to you — most people find the ampersand faster to type.

Combining columns with different data types (numbers and text)

If one of your columns contains numbers, the ampersand and CONCATENATE still work fine. Excel treats the numbers as text when you join them. For example, if A1 contains "Order" and B1 contains the number 5042, then =A1&B1 produces "Order5042".

The only time this causes problems is if you need to do math with those numbers later. Once they are joined as text, you cannot add them or use them in calculations. If you might need the original numbers for math, keep them in separate columns and only use the combined column for display or reference.

Removing the original columns after combining

Once your combined column is working, you might want to delete the original columns to clean up your spreadsheet. But if you delete them while the combined column still contains formulas, the formulas will break and show #REF! errors.

First, select all the cells in your combined column that contain the formula. Copy them (Ctrl+C on Windows, Command+C on Mac). Then right-click on the same cells and choose "Paste Special". Click the "Values" option and click OK. This converts the formulas into plain text, so the combined data stays even if you delete the original columns.

Now you can safely delete the original columns. Your combined column will keep all the text you created.

Combining columns with spaces or punctuation between them

You can add any text you want between the combined columns. Put whatever you want inside quotation marks. For a space: =A1&" "&B1. For a comma and space: =A1&", "&B1. For a dash: =A1&"-"&B1.

You can also combine more than two columns this way. If you have first name in A, middle initial in B, and last name in C, use: =A1&" "&B1&" "&C1. Each piece of text or cell reference is connected with an ampersand, and any text you want to appear between them goes in quotation marks.

Fixing common mistakes

If your combined column shows the formula text instead of the result (like "=A1&B1" instead of the actual combined text), you probably have the cell formatted as text. Right-click on the cell, choose "Format Cells", click the "Number" tab, and change the format to "General". Then press Enter to recalculate.

If you see #REF! errors, it usually means you deleted one of the original columns that the formula was pulling from. If you have not yet converted to values, undo the deletion (Ctrl+Z) and follow the "Paste Special as Values" steps above before deleting again. If you already deleted the column, you will need to recreate the formula using the columns that still exist.

Frequently Asked Questions

Can I merge columns without losing data?

Yes — do not use the merge cells feature. Use a formula instead (ampersand or CONCATENATE) in a new empty column. This keeps all your original data and creates a combined version in a separate column.

What if I want to combine more than two columns?

Use the same method with more ampersands or more arguments in CONCATENATE. For three columns: =A1&B1&C1. Add spaces or punctuation in quotation marks between them: =A1&" "&B1&" "&C1.

Do I have to keep the original columns after combining?

No, but you must convert the combined column to values first (copy, then Paste Special as Values) so the formulas do not break when you delete the original columns. After that, the combined data will stay even if the source columns are gone.

Why does my formula show as text instead of the result?

The cell is probably formatted as text. Right-click the cell, choose Format Cells, set it to General format, and press Enter. The formula will then calculate and show the combined result.

Can I combine text and numbers in the same formula?

Yes, the ampersand and CONCATENATE both treat numbers as text when combining. Just use them the same way: =A1&B1 works whether A1 and B1 contain text, numbers, or both.