- You can’t directly combine two columns in Excel, but luckily there’s a workaround using formulas and tools integrated into Excel’s software.
- If you try to use Excel’s merge function on two columns, you’ll get an error message warning that merging cells will only keep the data from the upper-left cell.
- If you’re trying to combine two empty columns, then the merge function will work.
- Here’s you how you can combine two columns in Excel.
- Visit Business Insider’s homepage for more stories.
Excel is a great program with hundreds of helpful functions. Unfortunately, one function it’s lacking is a simple way to merge two or more columns while preserving all of their data.
If you try to use Excel’s merge function on two columns, you’ll get a warning message that merging two cells will only keep the data from the upper-right cell, and therefore the data from all of your other cells will be lost.
Luckily, there is a workaround using formulas and tools that will allow you to combine two columns of data into one. It requires several steps, but it isn’t too tricky to follow.
Check out the products mentioned in this article:
Microsoft Office (From $129.99 at Best Buy)
MacBook Pro (From $1,299.99 at Best Buy)
Lenovo IdeaPad 130 (From $299.99 at Best Buy)
How to combine two or more columns in Excel
1. In Excel, click the “Insert” tab in the top menu bar. Then, click “Table.”
2. In the "Create Table" dialog box that pops up, edit the formula so that only the columns and rows that you want to combine are used in the table. For example, if you wanted to combine F1 and G6, your formula would read "=$F$1:$G$6" Additionally, if your columns have headers, make sure that the box indicating such is checked. Then click "OK."
3. Insert a new column into the table by either right-clicking on any part of the table and selecting "Insert" and then "New column" or by clicking and dragging the little green dot at the corner of your table out into the blank space next to it, if there is any.
4. In the first cell of your new column, write the following formula: =CONCATENATE(F2, " ", G2).
5. The blank space in quotes indicates a spacer, and in this example "F2" and "G2" are placeholders representing the names of the cells that you wish to combine. If you were trying to combine the cells beneath these, you would write "F3" and "G3" or if you were trying to combine the two cells to the left of these you would write "D2" and "E2."
6. Next, convert your formula into a value so that you can remove any unneeded columns. First, select your new merged column, either by holding "Ctrl" on your keyboard and clicking and dragging to select all of the cells in the column or by holding "Ctrl" + "Shift" + the down arrow on your keyboard and clicking the first cell of the column on your PC. On your Mac you can do this by either holding "command" and clicking and dragging or holding "command" + "shift" + the down arrow and clicking.
7. Copy the contents of the column to your clipboard by holding "Ctrl" + "C" on your PC keyboard or "command" + "C" on your Mac keyboard. You can also right-click and select "Copy" from the menu on either a PC or Mac.
8. Right-click on any cell in your selected column and select "Paste Special" from the menu.
9. In the "Paste Special" menu, click the bubble next to "Values," then click "OK."
10. Next, select the columns that you no longer need by clicking the first cell in the middle column, and pressing "Ctrl" + "Shift" + the down arrow, then "Ctrl" + "Shift" + the left arrow on your PC keyboard. On your Mac keyboard, press "command" + "shift" + the down arrow and then "command" + "shift" + the left arrow.
11. Once selected, right-click on any cell within the selection and choose "Delete" from the drop-down menu.
Related coverage from How To Do Everything: Tech:
-
How to copy a formula in Microsoft Excel using a Mac or PC
-
How to merge and unmerge cells in Microsoft Excel in 4 ways, to clean up your data and formatting
-
How to alphabetize data in an Excel spreadsheet by column or row, and by using shortcuts
-
How to lock cells in Microsoft Excel, so people you send spreadsheets to can't change certain cells or data
-
How to freeze a row in Excel so it remains visible when you scroll, to better compare data on different parts of a spreadsheet