Graphed LogoGraphed
Login

How to Merge 2 Columns in Excel Without Losing Data

Cody Schneider•

Trying to merge two columns in Excel often comes with a frustrating warning: you’re about to lose your data. If you’ve ever tried to combine a "First Name" and "Last Name" column only to end up with just the first name, you know the feeling. This article will show you several ways to merge columns in Excel correctly, without deleting any of your important information.

Why "Merge & Center" Is the Wrong Tool for the Job

First, let's address the most common mistake. On the "Home" tab of the ribbon, there’s a button called "Merge & Center." Many people assume this is for combining the data in columns, but it's designed for formatting, not data manipulation. Its purpose is to take several cells and turn them into one large cell, typically for creating titles or headers.

If you highlight two cells with data in them, say A2 ("Marcus") and B2 ("Aurelius"), and click "Merge & Center," Excel flashes a warning: "Merging cells only keeps the upper-left value and discards other values."

If you click OK, you'll be left with a single, wide cell containing only "Marcus." The data in cell B2 ("Aurelius") is gone for good. So, while Merge & Center is great for report titles, it's the wrong tool for combining data from multiple columns.

Luckily, there are several simple, data-safe ways to get the job done.

Method 1: Combine Columns with the CONCATENATE Function

Formulas are the most reliable way to combine columns in Excel because they are non-destructive. You create the combined data in a new column, leaving your original columns completely untouched.

The classic function for this task is CONCATENATE. It simply joins text, numbers, or cell references together into a single string.

Let’s say you have first names in column A and last names in column B, starting in row 2.

Step-by-Step Instructions:

  1. Click on the first empty cell where you want your combined names to appear (in this case, C2).

  2. Type the following formula: =CONCATENATE(A2, B2)

  3. Press Enter. Your result in C2 will be "MarcusAurelius."

That’s a good start, but it’s missing a space. To add a space (or any other text), you need to put it inside double quotes within the formula.

To add a space:

  1. Modify the formula in C2 to: =CONCATENATE(A2, " ", B2)

  2. Press Enter. Now, the cell correctly shows "Marcus Aurelius." The " " tells Excel to insert a space character between the content from A2 and B2.

Apply the Formula to the Entire Column:

To apply this to all your rows, you don't need to retype it. Click on cell C2, then look for the small green square at the bottom-right corner of the cell (this is called the fill handle). Double-click the fill handle, and Excel will automatically copy the formula down for every row that has data in the adjacent columns.

Method 2: Use the Ampersand (&) for a Quicker Formula

For a quicker and often cleaner way to achieve the same result, you can use the ampersand symbol (&) as a joining operator. It does the exact same thing as the CONCATENATE function but requires less typing.

Using the same data setup from before:

Step-by-Step Instructions:

  1. Select cell C2.

  2. Type the formula: =A2 & " " & B2

  3. Press Enter. You'll get the same perfect result: "Marcus Aurelius."

This method is more flexible for creative combinations. For example, if you wanted the name to appear as "Last Name, First Name" you could use this formula:

=B2 & ", " & A2

This would produce the result: "Aurelius, Marcus."

Again, you can use the fill handle to drag this formula down and apply it to your entire dataset. For most simple merges, the ampersand is the preferred method among Excel users due to its brevity and clarity.

Method 3: Go Modern with the TEXTJOIN Function

If you're using Excel 2019, Microsoft 365, or Excel for the web, you have access to a newer, more powerful function: TEXTJOIN. This function is fantastic when you need to combine multiple columns with the same separator.

The syntax is: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  • Delimiter: The character you want to place between each value (like a space, comma, or dash).

  • ignore_empty: A TRUE/FALSE value. If TRUE, Excel will skip any empty cells in your range. This is incredibly useful for addresses or contact lists where some fields might be blank.

  • text1, text2, ...: The cells or range of cells you want to combine.

Let's combine a full name and a department from columns A, B, and C.

Step-by-Step Instructions:

  1. Select cell D2.

  2. Type the formula: =TEXTJOIN(" ", TRUE, A2:B2) This will combine the range in ONE go, adding a space and ignoring blanks.

  3. Press Enter. Result: "Marcus Aurelius".

This might seem overkill for two columns, but imagine you have an address spread across five columns (Street, City, State, Zip, Country). TEXTJOIN makes it incredibly simple:

=TEXTJOIN(", ", TRUE, A2:E2)

This is far cleaner than a long, nested CONCATENATE or a chain of ampersands.

Method 4: The Formula-Free Approach with Flash Fill

If you prefer to avoid formulas altogether, Excel has an almost-magical feature called Flash Fill (available in Excel 2013 and newer). Flash Fill recognizes the pattern in your data as you type and automatically completes the work for you.

Step-by-Step Instructions:

  1. Next to your data (in cell C2), manually type the desired result for the first row. For example, type "Marcus Aurelius".

  2. Press Enter and move to the cell below (C3).

  3. Start typing the name for the second row, "Seneca". As you type, Excel should detect the pattern and show a grayed-out preview of the combined names for the rest of your list.

  4. If the preview looks correct, simply press Enter to accept it. The entire column will be populated instantly.

If the preview doesn't appear automatically, you can trigger Flash Fill manually. After typing your first example in C2, click on cell C3, go to the Data tab on the ribbon, and click Flash Fill. You can also use the keyboard shortcut Ctrl + E.

Flash Fill is incredibly fast and intuitive, but be aware that it’s not dynamic. If you change a first or last name in your source columns, the Flash Fill column will not update automatically. You would need to re-run it.

Final Step: Convert Your Formulas to Static Values

Once you use a formula to merge your columns, the new column is dependent on the originals. If you delete the "First Name" and "Last Name" columns, your combined column will show an error.

To make your new, merged column permanent and independent, you need to replace the formulas with their results. It’s a simple copy and paste operation.

How to Paste as Values:

  1. Highlight the entire new column that contains your formulas.

  2. Copy it (Ctrl + C or right-click > Copy).

  3. With the column still highlighted, right-click on it, and under "Paste Options," select the icon that looks like a clipboard with numbers (Values).

This replaces the formulas with the actual text results. Now you can safely delete the original columns you no longer need. This step is not necessary if you used Flash Fill, as it produces static values from the start.

Final Thoughts

Merging columns in Excel is a straightforward process once you know to avoid the restrictive "Merge & Center" tool. For combining data without loss, you can use the reliable CONCATENATE and ampersand methods, the more modern TEXTJOIN function for complex merges, or the lightning-fast, pattern-detecting Flash Fill for quick tasks.

Wrestling with manual data manipulation in spreadsheets costs a lot of time - time that could be spent analyzing information instead of just organizing it. At Graphed, we eliminate this friction by allowing you to instantly connect data sources and build real-time marketing and sales dashboards with simple, natural language. It’s like having an AI data analyst who automates the tedious Excel work, so you can go from data to decisions in seconds. Instead of wrangling formulas, you just ask Graphed for the report you need, and it’s done.