Have you ever needed to combine data from two columns in Excel but weren’t sure where to start?
Merging columns can be very useful for various situations, such as for creating full names from columns with first and last name, combining address data, creating a unique mapping key, etc.
In this beginner-friendly and step-by-step article, we will cover multiple methods (YES, with supporting screenshots!), such as:
- formulas
- concatenation
- built-in Merge
| Practice Excel Workbook (clean) | Download Here |
| Practice Excel Workbook (edited) | Download Here |
Understanding the Basics of Merging Columns in Excel
Since “merging” can refer to different techniques depending on your end goal, merging columns in Excel can become confusing. In this section, I will explain the difference between merging cell content and merging cells.
- Merging Cells:
- combine multiple cells into one large cell, typically for formatting purposes. For instance, you might want to create a centered header across several columns. This will not combine the actual data within the cells but will merge them into a single, larger cell.
- Merging Cell Content:
- combine the data from two or more cells into a single cell. Let’s say you might have a first name and a last name in two separate columns, and you want to combine them to create a single “Full Name” column.
Both methods are useful in different scenarios, and knowing which method is best suited for your specific task is important.
When to Use Each Method: Merging Cell Content vs. Merging Cells
- Merging Cells: best used for formatting purposes, commonly used in headers or tables.
- in the workbook provided above, add a row before the headers, then head over “Home” tab, under “Alignment” and you should be able to find a tool named “Merge & Center”.
- In cell A1, insert the text “Sales Data”, in column J1 insert the text “Demographics Data”.
- Select cells A1 to I1 and click “Merge & Center”. Do the same for J1:K1, then format them as below.
Important Note: Merging cells in this way can sometimes lead to issues when sorting or filtering data, as Excel treats the merged cell as one cell, which can cause alignment and data accessibility issues.
- Merging Cell Content: ideal when you need to combine data from two or more columns into a single column preserving each cell’s data. For this purpose, excel gives us 3 tools:
- CONCAT function, used for quick and easy merges ranges of cells
- the & operator, allowing for intermediary ties, such as spaces or commas
- TEXTJOIN function, used for combining multiple columns with a delimiter.
- Using Excel’s Flash Fill Feature
- Type the desired merged data in the desired cell.
- Double-click on the corner to copy it down
- An Auto Fill Options button will appear, click then select “Flash Fill”.
- Whilst this method is easy, it is not sustainable for large datasets or dynamically updating data.
How to Handle Common Issues When Merging Columns
By using certain formulas we can avoid extra spaces when one column could be empty.
Using the formula below it ensures that empty cells are overlooked and not taken into consideration, this is called conditional merging.
Remember to copy your merged results as values to keep them if you plan on deleting the original columns by simply selecting the range(or whole column), press CTRL+C then ALT+H+V+V to paste values.
Choose the Right Method for Your Needs
Merging data in Excel from different columns can streamline your workflow, which makes it easier to analyze and present data effectively, or can be very helpful in managing unique mapping tables or simply concatenating columns in Excel.