The question of how to combine columns in Excel comes up often, and there are multiple ways to do it. This article will discuss a couple of quick and easy approaches to get you going, and you can choose what works best for you.
How to Combine Columns in Excel
1. Insert a new empty column that will ultimately contain your outcome – combined information from two or more columns.
2. Use one of the methods in this article to populate the first cell in your new column.
3. Auto fill the rest of your new column.
That’s it! Your new combined column can be built in a matter of seconds. Now the secret sauce is in step 2 – how you create the first cell in the new column. Here are two methods you can choose from:
The Concatenate Function. Excel has a built in function called CONCATENATE that is used to combine things together. Let’s look at an example of using concatenate with names. Suppose you have column A containing first names and column B containing last names, and you would like to combine them into a 3rd column that displays full names. Recall that the first step is to insert a new empty column, say column C. Then using the concatenate function, you would type the following formula in cell C1 and press enter:
=CONCATENATE (A1,” “, B1)
Upon pressing the enter key, Excel will combine the list of contents in the parentheses, replacing cell names with their actual values. The space inside quotes ensures that a space is inserted between the values for A1 and B1. If A1 contained Pablo, and B1 contained Picasso, then the result in C1 would be Pablo Picasso. Now you can select cell C1 and finish out the rest of the new column using auto fill.
The Ampersand Method. If the concatenate function seems unintuitive to you, another option is to use the ampersand (&) character to tell Excel exactly what to combine together. The same pieces of text and cell names will be put together, with ampersands between them instead of commas. For the example above, you could alternatively type the following in cell C1 using ampersands:
=A1&” “&B1
Upon pressing enter, the result displayed will be Pablo Picasso. Again, you may auto fill the rest of column C and you’re done!
The part in quotes in this example is just a space, but you can put any text, characters, or symbols in quotes and combine them in any order. For example, typing =CONCATENATE(A1,” “,B1,”, the great!”) or =A1&” “&B1&”, the great!” would display Pablo Picasso, the great!
You might want to keep your original columns. When combining two columns, you may want to keep the two original columns and continue to add and update data in those columns. The new combined column will be automatically updated when you update the first two columns. But if you don’t see ever needing the original columns, you can also delete them and just update the new column. Good spreadsheet design is flexible and allows you to quickly specialize your views and obtain information using tricks like concatenate and ampersand.