Excel 2013 makes it possible for you to automatically generate and combine data that you have already entered into your spreadsheet. One way that you can do this is the CONCATENATE formula, which allows you to combine three columns into one in Excel.
This is a powerful Excel tool to know, as it can help to eliminate a lot of wasted time. Once you have familiarized yourself with the formula and can use it to combine multiple cells into one, you can really expedite and eliminate a lot of tedious data entry that might have been taking up a lot of your time.
Our guide below is going to walk you through setting up and customizing the CONCATENATE formula so that you can combine multiple columns into one in Excel.
How to Combine Three Columns in Excel
- Open your spreadsheet.
- Select the cell where you want to display the combined data.
- Type =CONCATENATE(AA, BB, CC) but insert your cell locations. Press Enter when done.
- Adjust the formula to include any needed spaces or punctuation.
- Copy and paste the formula in the rest of the cells where you wish to combine data.
Our article continues below with additional information and pictures of these steps.
How to Merge Three Columns Into One in Excel
The steps below were performed in Excel 2013, but will also work for other versions of Excel. Note that we will show you how to do the basic formula that combines data from multiple cells, then we will show you how to modify it to include things like spaces and commas. This specific example will combine a city, state, and zip code into one cell.
Step 1: Open your spreadsheet in Excel 2013.
Step 2: Click inside the cell where you want to display the combined data.
Step 3: Type =CONCATENATE(AA, BB, CC) but replace the AA with the cell location from the first column, BB with the cell location from the second column, and CC with the cell location from the third column. Press Enter to complete the formula.
At this point your data might just be one long string of text, which isn’t useful in certain situations. We can fix this by including some additional parts in the CONCATENATE formula. I am going to modify the formula for the data above so that I get a result that looks like Phoenix, AZ 85001 instead of PhoenixAZ85001.
Step 4: Modify the formula with any required spaces or punctuation. Our formula in this tutorial will now be =CONCATENATE(A2, “, “, B2, ” “, C2).
Note that there is a space after the comma in the first set of quotation marks, and a space between the second set of quotation marks.
Step 5: Apply this formula to the rest of the cells in this column by clicking the handle at the bottom-right corner of the cell, then dragging it down to select the rest of the cells.
- The cells do not have to be in this order. You could modify the formula to be =CONCATENATE(CC, AA, BB) or any other variation.
- Updating the data in one of the original, uncombined cells will cause that data to update automatically in the combined cell.
- If you need to copy and paste this combined data into another spreadsheet or a different worksheet, you might want to use the “Paste as text” option, otherwise the data might change if you adjust it after pasting.
- The method isn’t limited to just two or three columns. You can modify the formula as needed to include a very large amount of data or columns, depending on what you need to do.
Learn about using the VLOOKUP formula in Excel for a some helpful ways to search for data and include it in cells more efficiently.
- How to subtract in Excel
- How to sort by date in Excel
- How to center a worksheet in Excel
- How to select non-adjacent cells in Excel
- How to unhide a hidden workbook in Excel
- How to make Excel vertical text
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.