Get the Combine Columns Excel Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice all 5 methods from the tutorial with real data
- See each method set up with examples you can test right away
- Includes a Challenge tab so you can check what you learned
Enter your email below for instant access.
How to Combine Columns with the Ampersand (&)
The ampersand operator lets you join text from different cells quickly.
Steps:
- Click on the cell where you want the combined data, e.g. C2.
- Type the formula:
=A2 & " " & B2. ReplaceA2andB2with the cells you want to merge.
- Type the formula:
- Use
" "to add a space, or", "for a comma. - Press Enter.
Drag the fill handle down to copy the formula to other rows.
Example:
If A2 has “Sheldon” and B2 has “Cooper,” the formula will create “Sheldon Cooper”.
Looking to merge the cells themselves rather than combine their values? See our guide on how to merge cells in Excel for formatting and layout options.
How to Use CONCAT to Combine Columns
The CONCAT function works like the ampersand but is easier to use for many columns.
Steps:
- Select the target cell.
- Enter the formula:
=CONCAT(A2, " ", B2). - Press Enter and use the fill handle to copy it down.
💡 Tip: If your Excel version is older, you can use CONCATENATE instead.
⚠️ Technical Note: Avoid #NAME? Errors
If you are using Excel 2016 (standalone), 2013, or older, the TEXTJOIN and CONCAT functions will return a #NAME? error. These functions require Microsoft 365 or Excel 2019 and newer.
Older versions only support the & symbol or the older CONCATENATE function.
Pro Tip: Use the Ampersand (&) method for maximum compatibility across all Excel versions and Google Sheets.
How to Combine Multiple Cells with TEXTJOIN
TEXTJOIN is perfect for situations where you need to combine multiple cells with a separator. For example, creating a list of items.
Scenario:
- Suppose you have a list of names in cells A2 to A7.
- You want to create a single cell, e.g., B2 that lists all tasks separated by commas.
Steps:
- Click on an empty cell where you want the combined data, e.g., B2
- Use the TEXTJOIN function. For example, if your names are in cells A2 through A7
=TEXTJOIN(", ", TRUE, A2:A7)This formula will combine all tasks into one cell, with each task separated by a comma and a space.
Combined columns are also useful as a source for drop-down lists in Excel. For example, you could merge a first name and last name column, then use that combined list as the source for a data validation drop-down.
If you need to combine columns across multiple worksheets, see our guide on looking up values across multiple worksheets.
Working with multilingual data? After combining columns, you can use the TRANSLATE function to convert text between 100+ languages directly in Excel.
How to Combine Columns with Flash Fill (Ctrl+E)
Flash Fill is an automated way to merge data.
Steps:
- Manually type the combined result in the first row (e.g., “Sheldon Cooper”).
- Start typing in the second row. Excel will show a preview.
- Press Enter to accept it.
💡 Tip: Make sure Flash Fill is enabled under Data > Flash Fill.
Limitations of Flash Fill
- Not Dynamic: Flash Fill results are static and don’t update if the original data changes.
- Placement Restriction: Combined data must be in the same rows, directly beside the source columns.
- Pattern Sensitivity: Flash Fill may fail if the input pattern is inconsistent.
- No Error Handling: It processes data as-is, so clean your data first.
If you want to learn more about Flash Fill, click here.
How to Merge Columns with Power Query
Power Query is one of the best tools for combining columns in Excel, especially if you’re working with large datasets.
Here’s why:
- Handles Big Data Easily: It processes large datasets quickly without slowing down your computer.
- Keeps Data Updated: If your source data changes, Power Query updates the results automatically.
- Safe for Original Data: Your original data stays untouched, so you don’t have to worry about mistakes.
- Customizable Options: You can filter, clean, and format your data while combining it—all in one step.
Want to know why Power Query is a must-have skill for Excel users? Click here to learn more.
Download the Free Practice File
Mastered the basics? Now practice!
Download the toolkit and try the ‘Challenge’ tab to see how much you’ve learned.
Click here to get your Practice Workbook →Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
