How to Combine 2 Columns in Excel: Step-by-Step Guide

Download the practice workbook HERE and follow along.

Combining two columns in Excel is an essential skill for anyone working with data. Whether you need to merge first and last names, combine codes and descriptions, or simply consolidate information from different columns, Excel offers several straightforward methods to achieve this. This guide will walk you through the easiest and most effective ways to combine columns in Excel, ensuring your data is organized just the way you need it. Let’s explore these methods step-by-step.

Combine Two Columns Using the Ampersand (&) Symbol

The ampersand (&) symbol is a quick and simple operator in Excel that allows you to join text strings from different cells. It’s perfect for basic column combinations and offers a straightforward approach for beginners.

Steps:

• Select the Target Cell: Click on the cell where you want the combined data to appear. For example, if you want to combine data from column A and column B starting from row 2, you might select cell C2.
• Enter the Formula: Type the formula using the ampersand symbol. The basic syntax is =Cell1&"separator"&Cell2.
  • Replace Cell1 and Cell2 with the actual cell references you want to combine (e.g., A2 and B2).
  • Use quotation marks " to add separators like a space " " or a comma and a space ", " between the combined text.
  • For instance, to combine the content of cell A2 and B2 with a space in between, you would type: =A2 & " " & B2.
• Press Enter: After typing the formula, press the Enter key. The combined text from the specified cells will now appear in your selected cell.
• Apply to Other Rows: To combine data in subsequent rows, simply use the fill handle. Click on the small square at the bottom-right corner of the cell containing your formula, then drag it down to apply the formula to the rest of the rows.

Example:

Suppose cell A2 contains “John” and cell B2 contains “Doe”. By entering the formula =A2 & " " & B2 in cell C2, the result in C2 will be “John Doe”.

Use the CONCAT Function

The CONCAT function in Excel is specifically designed for concatenating (joining) text strings. It provides a more structured way to combine columns compared to the ampersand operator, especially when dealing with multiple columns or ranges.

Steps:

1. Choose the Output Cell: Select the cell where you want the merged content to be displayed.
2. Input the CONCAT Formula: Enter the formula =CONCAT(Cell1, "separator", Cell2, ...) into the selected cell.
   • Replace Cell1, Cell2, etc., with the cell references you wish to combine.
   • Include separators like " " for spaces or ", " for commas and spaces within quotation marks as needed between cell references.
   • For example, to combine cells A2 and B2 with a space, the formula would be: =CONCAT(A2, " ", B2).
3. Press Enter and Fill Down: Press Enter to execute the formula. Then, use the fill handle (drag the small square at the bottom-right of the cell) to copy the formula down to apply the combination to other rows.

Tip: If you are using an older version of Excel, you might encounter the CONCATENATE function instead of CONCAT. CONCATENATE works similarly but might be slightly less flexible with array inputs compared to CONCAT. For most column combining tasks, either function will work effectively.

Merge Data with TEXTJOIN

TEXTJOIN is an excellent function for combining text from multiple cells, especially when you need to include a delimiter and handle empty cells efficiently. This function is particularly useful for creating lists or sentences from data spread across columns or rows.

Scenario:

Imagine you have a list of items in cells A2 through A7 and you need to combine them into a single cell, separated by commas and spaces.

Steps:

• Select the Destination Cell: Choose an empty cell where you want the combined list to appear (e.g., B2).

• Use the TEXTJOIN Function: Enter the TEXTJOIN formula. The syntax is =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).

  • delimiter: Specify the character or string you want to use as a separator between the combined items, enclosed in quotation marks (e.g., ", ").
  • ignore_empty: Set to TRUE to ignore empty cells in the selected range, or FALSE to include delimiters for empty cells.
  • text1, [text2], ...: Enter the cells or range of cells you want to combine. For our scenario, it would be A2:A7.

  For example, to combine names from cells A2 to A7 separated by commas and spaces, ignoring empty cells, the formula would be:

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

• Press Enter: Press Enter to apply the formula. The cell B2 will now contain all the names from A2 to A7, combined into a single string, separated by commas and spaces.

Note: The TEXTJOIN function is available in Excel 2019 and later versions, as well as in Microsoft 365. If you are using an older version of Excel, consider using CONCATENATE or ampersand (&) for simpler combinations, or upgrade to a newer version to take advantage of TEXTJOIN for more complex merging tasks.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

This course will help you master FILTER, SORT, SEQUENCE, and other new functions added to Excel. You’ll create reports in a fraction of the time it used to take you.

Learn More

Flash Fill for Quick Results

Flash Fill is an incredibly efficient and user-friendly feature in Excel that automatically recognizes patterns in your data to help you combine columns without needing to write formulas. It’s perfect for quick, on-the-fly combinations when you can demonstrate the desired output with an example.

Steps:

1. Create an Example: In a column next to your data columns, manually type the combined result you want for the first row. For instance, if you’re combining first and last names from columns A and B, in cell C2, type the full name as you want it to appear (e.g., “Sheldon Cooper”).
2. Initiate Flash Fill: Start typing the combined name for the second row in cell C3. As you begin typing, Excel will often recognize the pattern and show a preview of the Flash Fill results in light gray below.
3. Accept Suggestions: If the preview accurately reflects the combined data you want for the rest of the rows, press Enter to accept the Flash Fill suggestions. Excel will automatically fill in the combined values for the remaining rows based on the pattern it detected.

Tip: If Flash Fill doesn’t start automatically, or if the suggestions aren’t appearing, ensure Flash Fill is enabled. Go to Data in the Excel ribbon and click on Flash Fill in the Data Tools group. You can also use the shortcut Ctrl + E to manually trigger Flash Fill.

Limitations of Flash Fill

While Flash Fill is incredibly convenient, it’s important to be aware of its limitations:

• Static Results: Flash Fill outputs are not dynamic. If you change the original data in columns A or B, the combined values generated by Flash Fill will not automatically update. You’ll need to re-apply Flash Fill to update the results.
• Row Dependency: Flash Fill works based on patterns in adjacent rows and is best suited when the combined data needs to be in the same row as the source columns, directly beside them.
• Pattern Sensitivity: Flash Fill relies on recognizing consistent patterns. If your data is inconsistent or lacks a clear pattern, Flash Fill might not produce the desired results or may require manual correction.
• No Error Handling: Flash Fill processes data as it is. It doesn’t inherently clean or correct data. Ensure your source data is reasonably clean and consistent before using Flash Fill for best results.

If you’re interested in learning more about maximizing Flash Fill’s potential and understanding its nuances, click here for a detailed guide.

Merge Columns with Power Query

Power Query is a powerful data transformation and manipulation tool within Excel, ideal for combining columns, especially when working with large datasets or when you need to perform more complex data operations during the merge process.

Here’s why Power Query is a robust solution for combining columns:

• Handles Large Datasets Efficiently: Power Query is designed to handle and process large volumes of data without significantly slowing down Excel, making it perfect for extensive spreadsheets.
• Dynamic Updates: Power Query connections can be refreshed. If your original data sources change, you can easily refresh your Power Query query to update the combined columns automatically, ensuring your data is always current.
• Non-Destructive Data Manipulation: Power Query operations are non-destructive. Your original data remains untouched, and the combined results are outputted to a new location, preserving data integrity and allowing for iterative adjustments.
• Advanced Data Transformation: Beyond just combining columns, Power Query allows you to perform a wide range of data cleaning, filtering, and transformation steps as part of the merging process. This includes splitting columns, changing data types, and more, all within a single, streamlined workflow.

Want to explore the full potential of Power Query and understand why it’s considered a must-have skill for modern Excel users? Click here to learn more about our comprehensive Excel Power Query course.

Featured Course

Master Excel Power Query – Beginner to Pro

With Power Query, you can quickly turn messy data into smart decisions – fast! Automate repetitive tasks and create reports in a fraction of the time.

Learn More

Download the Free Practice File

Ready to put your knowledge into practice and master combining two columns in Excel? Download our free practice workbook designed to accompany this guide. This workbook includes hands-on exercises to help you solidify your skills and explore different column combining techniques.

Inside the workbook, you’ll find:

• Practical Exercises: Practice combining columns using various methods discussed in this guide, including formulas like TEXTJOIN and CONCAT, as well as using Flash Fill.
• Real-World Scenarios: Work through realistic examples that mimic common data manipulation tasks, helping you understand how to effectively clean and merge data in practical situations.
• Efficiency Tips: Discover time-saving shortcuts and best practices that will make combining columns in Excel faster and more efficient, regardless of your experience level.

Download the Workbook Now to start enhancing your Excel skills today! Whether you’re a beginner or an experienced Excel user, this practice file is designed to help you master the art of combining columns and improve your data management capabilities.

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses (Plus bonuses ONLY available here). You’ll learn high-value, in-depth Excel skills that solve real problems. NOT surface-level stuff.

Learn More

Published on: November 29, 2024

Last modified: November 29, 2024

Category: Excel

Tagged as: combine cells in excel, combine columns in excel, how to merge two columns in excel

LinkedIn Twitter YouTube

Leila Gharani

I’ve spent over 20 years helping businesses use data to improve their results. I’ve worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.

As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.

I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.

More About Leila Join 400,000+ professionals in our courses