If you are one of those folks toiling on excel sheets to make calculations for your client or enter product sales data, then this one is definitely for you.
Ever been stuck on a problem where you need to combine multiple cells or columns? And you’re tired of typing it out for every cell? Well, that’s not the way to do it! This guide will help you to learn how to combine two columns or multiple columns in Microsoft Excel.
Microsoft Excel is a software tool provided by Microsoft Corporation used for creating and editing an excel worksheet. In this age of plethora of data, it becomes very easy to manage complex information using spreadsheets. Adding excel skills to your toolbox can give you an edge over others in the industry. Learn how to install Microsoft Office for mac here.
There are multiple ways to merge cells or to merge columns entirely. Learn how to combine two columns in excel.
Use Ampersand Symbol for Merging Cells
Using Ampersand symbol (&) is an easy way to merge columns in excel without losing data.
How to combine two cells in Microsoft Excel using &
- Consider you wish to combine two cells, A2 & B2.
- Firstly, double click on the cell on which you wish to get the combined data from multiple columns. This is referred to as a combined cell.
- Type ‘=’ without the quotation marks.
- Now click on the first cell you wish to combine.
- The value of the address of this cell will appear on the combined cell in which you want the combined data.
- Now in the same cell, type the ampersand ( & ) symbol.
- Now click on the second cell which you wish to combine with the first one. The value of the address of this cell will also appear beside & in the total cell. For example, suppose you wish to combine the cells with addresses A2 and B2 and store the combined data in cell C2. The following formula with the address values should appear something just like this =A2 & B2.
- Press Enter.
- Booyah! The combined data from the two cells appear in your combined cell where you wrote the formula. The strings from the two columns in excel are concatenated and displayed as one cell. Now are all set to see how to combine two columns in excel.
Combine Two Columns in Excel Using Concat Function
Another way to combine two cells without losing data is by using the Concat function, which is a text function. It gives a similar result to the above method. However, it involves a little more typing. Find out more below.
How to combine two columns in Excel using Concatenate Function
- Double click on the cell in which you wish to combine cells.
- Type ‘=’ without the quotation marks.
- Now type the following concatenate formula CONCAT ().
- In the bracket, you need to enter the addresses of the cells you wish to combine and get the combined cell. You can click on the first cell, then type a comma (,) then space, and then click the other cell you wish to combine. Separate the addresses by a comma (,) and a space. So for the above example, the concatenate formula with the address values looks like this ‘ = CONCAT ( A2, B2).’
- The cell shows the data of the combined cells in a single cell.
How To Combine Multiple Cells in a Row in Excel
Now how about combining multiple cells in excel using the above two functions instead of just two cells. This also sets primitive to the answer of the question about how to combine two columns in excel.
Steps to merge two or more cells in Excel using &
- The steps are similar to those in the case of two cells.
- Firstly double click on the cell you need to merge cells.
- Type ‘=’ and then click on the first cell and then type & symbol. Then click the second cell. Now again, type ‘&’ and then click on the other cell. This way, merge cells using & between their addresses.
- Consider an example that you wish to combine four selected cells, namely A2, B2, C2, and D2, in cell E2. Then the concatenate formula in cell E2 with the address values should look like this ‘= A2 & B2 & C2 & D2’ without the quotation marks.
- Click enter to get the cell in a new column with this combined data and cell address E2. E2 contains data of all the combined cells.
How to merge two or more cells in Excel using Concat function
- This is very similar to combining two cells.
- Double click on the cell in which you wish to merge data.
- Type a concatenate function‘ = CONCAT()’
- Now in the parenthesis, type the addresses of the cells separated by a space and a comma.
- For the above example, the formula with the address values looks like this ‘= CONCAT ( A2 & B2 & C2 & D2 )’ without the quotation marks.
- Press Enter.
Using both the above methods, you can add the addresses of the other cells to merge data and get the combined cells.
After combining cells in one row, this job appears pretty easy, right? But do you need a type for each row to combine two columns of data into a table of contents in excel? Don’t worry. It is a lot easier to join columns than you thought.
Large spreadsheets have a huge amount of data, and there could be hundreds or thousands of rows or even more. You definitely do not need to type these formulas for every single row to combine two or more columns. Find out below how to combine two columns in excel two merge the entire data.
How To Combine Two Columns in Excel
- Firstly, use any of the methods mentioned above to combine two or more cells that constitute the top cell of each column in excel. Consider these as the column header. Combine data using the Concat formula in the result column.
- Now to combine several columns of data like first name or last name, of which the column header is already merged, double click on the result cell.
- The formula you just typed appears on the cell.
- There is a green dot on the bottom right corner of this cell called fill handle. Hover your cursor over it and drag it down to combine columns of data in excel till all the rows for combining columns of data in excel.
- Cells in all the rows for which you drag down will display the merged column. All the selected columns get combined without losing data. You can combine two columns and add a third column of data too. You can also press ctrl to navigate to the last cell of the row or columns in excel using arrow keys.
This is how to combine two columns in excel.
Some special cases while combining columns in Excel
- You can easily combine two or more columns in excel. However, there can be no logic in combining a single column. So at least two parameters are required in the Concat formula as the addresses of the excel columns of data in all the methods mentioned above.
- If you only give one column’s address, then a dialog box appears, and an error message is displayed. At least two values of addresses should be given. Thereafter, you can also specify the addresses of the other columns to merge their data too.
- Suppose some cells in different columns are empty cells. In this case, the combination appears that involves only the other excel columns.
- Adding two empty columns results in a generation of empty cells.
- It should be noted every column is added one after the other. Hence, there is no space separator or any other separator or another symbol between any column’s contents.
- After combining the columns, you can change the alignment of the column, for which you need to select the complete column and then use the alignment tab. You can also use the other functions in the home tab for carrying out different operations on the combined column.
Typing the same data from the other columns is a more tedious job than you imagine and makes no sense whatsoever. Using the excel sheet makes it easier, and all the possibilities of typing errors while merging data are eliminated. And most importantly, a lot of time is saved that facilitates the other operations to be performed.
Hope now you can use the given information regarding excel merge columns into one cell.
Moreover, these functionalities work on both the desktop and mobile versions of Microsoft Office. In this way, two or more columns of data can be combined and finish the task of endless hours in a few clicks.