Combining columns in Excel is a vital skill for organizing and analyzing data effectively. Whether you're merging first and last names, combining data points, or creating seamless datasets, there are several techniques to get the job done. This guide explores six efficient methods, including using the CONCATENATE function, the Ampersand (&) operator, and newer tools like TEXTJOIN and Power Query. Learn how to combine two columns while avoiding common pitfalls like overwriting data.

By the end, you’ll have the tools you need to merge Excel columns efficiently and confidently.

Disclaimer: Always ensure you preserve your original data when merging columns to avoid accidental loss of information.

How To Merge Columns in Excel: 6 Different Methods

Combining data from two columns in Excel is simple and can be done using various techniques. Here are six effective ways to combine two columns and organize your data efficiently:

Method 1: Using the CONCATENATE Function

The CONCATENATE function (or the newer CONCAT function) allows you to combine data from two or more columns into one. It’s easy to use by entering a formula like =CONCATENATE(A1, " ", B1) to merge values with a space or other delimiter. This method is perfect for combining text such as names or addresses. Follow the below steps to Combine columns in Excel using the CONCAT Function:

Step 1: Select an Empty Cell

Click on a cell where you want the combined data to appear. Here In the below example, we have selected C2.

Step 2: Enter the Formula

In the Selected Cell, Enter the CONCATENATE Formula

=CONCATENATE(A2, " ", B2)

Or, using the newer CONCAT function:

=CONCAT(A2, " ", B2)

• A1: First column cell.
• B1: Second column cell.
• " ": Adds a space between the values (you can replace it with a comma or any delimiter).

Step 3: Press Enter

The two cells will be combined.

Step 4: Drag the Formula and Preview Result

Use the fill handle to apply the formula to other rows.

Method 2: Using the Ampersand (&) Operator

The Ampersand operator (&) is a simple and efficient way to combine data from two columns. By using a formula like =A1 & " " & B1, you can merge values with a space or any delimiter. It works similarly to CONCATENATE but is quicker to type and more intuitive.

Step 1: Enter the Formula

=A2 & " " & B2

Step 2: Press Enter and Preview Result

The two columns will merge into one.

Step 3: Drag the Formula

Use the fill handle to apply the formula to other rows.

Why Use Ampersand

• It’s easier and faster to type.
• Works the same as CONCATENATE but requires no function name.

Method 3: Using Flash Fill

Flash Fill is a quick and easy way to combine columns without using formulas. Simply type the desired result in the first row, and Excel automatically detects the pattern. Activate it by pressing Ctrl + E or using Data > Flash Fill to fill in the combined values for the rest of the rows. It’s ideal for small datasets.

Step 1: Enter the Combined Value Manually

In a new column, type the combined value for the first row (e.g., "Miley Barey").

Step 2: Enable Flash Fill

Go to Data > Flash Fill or press Ctrl + E.

Excel will automatically fill in the combined values for the remaining rows based on the pattern.

Advantages:

• No formulas needed.
• Simple and quick for small datasets.

Method 4: Using TextJoin Function

The TEXTJOIN function is a powerful way to combine multiple columns or ranges with a specified delimiter. It allows you to include or ignore blank cells, making it more efficient for merging large datasets. Follow the below steps to Merge two columns in excel using TextJoin Function:

Note: TEXTJOIN is available in Excel 2019 (and above), and Microsoft 365.

Step 1: Enter the Formula

=TEXTJOIN(" ", TRUE, A1:B1)

• " ": Specifies the delimiter (e.g., space, comma).
• TRUE: Ignores blank cells.
• A1:B1: Specifies the range of cells to combine.

Step 3: Press Enter

The two columns will merge, ignoring blank cells.

Why Use TEXTJOIN

• Ideal for merging multiple columns with customizable delimiters.
• Handles blank cells gracefully.

Method 5: Using Power Query

Power Query is a robust tool for combining columns, especially in large datasets. It allows you to merge columns with custom delimiters directly in the Power Query Editor. After combining, the data is loaded back into Excel, fully formatted and ready to use. This method is ideal for handling complex datasets efficiently. Follow the below steps to combine two Columns in Excel using Power Query:

Step 1: Select Your Data

Highlight the table with the columns to combine.

Step 2: Open Power Query

Go to Data > Get & Transform > From Table/Range.

Step 3: Select Columns

In the Power Query Editor, select the two columns.

Merge Columns:

• Click Transform > Merge Columns.
• Choose a delimiter (e.g., space, comma).

Step 4: Load Data Back

Click Close & Load to return the combined data to Excel.

Method 6: Using the "Merge" Icon to Merge Columns

The "Merge" feature in Excel combines the content of cells into one, but it has significant limitations, which is why it’s not commonly recommended for merging data columns effectively.

• Highlight the Cells: Select the range of cells across the columns you want to merge.
• Go to the Merge Icon: On the Home tab, locate the Merge & Center button.
• Choose a Merge Option:
  • Merge & Center: Combines the selected cells into one and centers the content.
  • Merge Across: Merges cells in each row individually.
  • Merge Cells: Combines cells without centering the content.

Why This Method Isn’t Commonly Recommended:

• Only Keeps the First Cell’s Content: When merging cells, Excel retains the content of the first cell and discards the rest, which means it doesn’t actually combine data from multiple cells.
• No Dynamic Combination: Unlike formulas or functions, the Merge feature does not dynamically update when the data changes.
• Breaks Table Structure: Merging cells can disrupt sorting, filtering, and referencing in Excel tables, making it unsuitable for structured datasets.
• Not Reversible Without Data Loss: Unmerging cells does not restore the lost content from other cells.

Common Errors and Solutions When Combining Columns

By addressing these issues, you can effectively combine columns without errors in tools like Google Sheets or Excel.

Conclusion

Mastering these six methods for combining two columns in Excel empowers you to handle data more effectively, whether through the simplicity of the Ampersand operator or the versatility of Power Query. Each approach has unique strengths, ensuring flexibility for various scenarios. Experiment with these methods to find the one that suits your needs, and take your Excel skills to the next level.

How to Combine Two Columns in Excel - FAQs

How do I merge two columns in Excel without losing data?

To merge columns without losing data, avoid using the "Merge & Center" feature as it discards all but the first cell's content. Instead, use functions like CONCATENATE or Ampersand (&) to combine values into a new column.

How do I CONCATENATE two columns of data in Excel?

To CONCATENATE two columns:

• In a new cell, type =CONCATENATE(A1, " ", B1) or use the updated =CONCAT(A1, " ", B1) formula.
• Press Enter and drag the formula down to apply it to the rest of the rows.
• This will combine the values from both columns with a space (or any delimiter you choose).

How to combine two columns in Excel with first and last names?

To combine first and last names into a full name:

• Use the formula =A1 & " " & B1, where A1 is the first name and B1 is the last name.
• Format the combined column as needed for display.
• This creates a single column with names like "John Doe".

What is the best function to combine columns in Excel?

The TEXTJOIN function is ideal for combining multiple columns, especially when handling blank cells. Use it like =TEXTJOIN(" ", TRUE, A1:B1) to merge values with a delimiter while ignoring blanks.

Can I merge two columns using the "Merge" icon in Excel?

The "Merge & Center" feature is not recommended for combining data because it discards all but the first cell's content. Use formulas or Flash Fill instead to preserve all data.