How to Merge Two Columns Data in Excel

Microsoft Excel is a versatile tool for managing and analyzing data, but often, data isn’t in the desired format when you import it from external sources. For example, names might be split into separate columns, and you might prefer to have the full name in a single cell. The good news is that you can easily merge columns in Excel, ensuring that your data is structured the way you want. Here’s a comprehensive guide on how to do it.

Method 1: Combining Columns Using Ampersands

A straightforward method to combine data from different columns in Excel is to use the ampersand (&) symbol. This symbol allows you to create a formula that references multiple cells and merges their data. You can also include additional text or spaces in the combination.

Follow these steps to combine columns using ampersands:

1. Open the Excel spreadsheet containing the columns you want to combine. Select the empty cell where you want the combined data to appear. If you’re combining multiple rows of data, start by selecting the top cell.
2. In the selected cell, type = to start a formula. Select the cell containing the first piece of data you want to include.
3. Type the & symbol.

4. If you want to include a space or any other character between the data, type ” “& or “,”&. For example (A1&” “&B1). Skip this step if no extra characters are needed.
5. Select the cell containing the second piece of data you want to include and press Enter.

6. To combine more than two columns, repeat steps 5-7 as needed.
7. To combine more rows of data in the same way, click and hold the drag handle in the bottom-right corner of the cell, then drag down over the cells where you want the combined data to appear.

8. Release the cursor, and the cells will autofill with the combined data.

Method 2: Combining Columns Using the CONCAT Function

The CONCAT function is another method to combine columns in Excel, and it’s the recommended approach as the CONCATENATE function is being deprecated. CONCAT offers a more modern solution.

Follow these steps to combine columns using the CONCAT function:

1. Open your Excel spreadsheet and select the cell where you want your first combined data to appear.
2. Type =CONCAT( to begin the formula.
3. Select the cell containing the first piece of data you want to combine.
4. If you want to insert a space or another character between your data, type ,” “ or “,”. For instance (=CONCAT(A1,” “,&B1). Proceed to the next step if no additional characters are required.
5. Type a comma (,) and then select the cell containing the second piece of data you want to combine and type ) and press Enter.

6. To combine more than two columns, repeat steps 3-5.
7. To apply the formula to other cells, click and hold the drag handle in the bottom-right corner of the cell. Drag down over the cells where you want the formula to apply.

8. Release the cursor, and the cells will autofill with the combined data.

9. Similar to the previous method, if you want the cells to contain just the combined data, replace the formula with the result.

Conclusion

Understanding how to combine columns in Excel is a valuable skill for data management. Whether you need to merge data from multiple columns or create new text from your data, these methods offer you flexibility and efficiency. Excel offers a multitude of powerful features beyond basic operations, such as capitalizing text, calculating ages from birthdates, or using financial functions to manage loans. By mastering these Excel techniques, you can enhance your data analysis capabilities.