There are two situations in which you might need to merge two columns of data in Excel 2013. If you've imported two columns of data from separate sources, you might want to combine them into a single column without linking the values themselves. Once combined, you should check for duplicates that existed in each source. Alternatively, you can merge the values of aligned cells, such as combining first and last names, and then locate duplicates based on the combined data.
Creating One Long Column
Click the first data cell in the second column to select it.
Press "Ctrl-Shift-Down Arrow" to highlight all of the data in that column. If there are gaps in the data, you might need to press the key combination multiple times to select it all.
Press "Ctrl-X" to cut the cells.
Click just beneath the last data cell in the first column.
Press "Ctrl-V" to move the second column's data to the bottom of the first column.
Merging the Values
Right-click the column header immediately to the right of the second column and select "Insert" to add a new column, if necessary. As an example, if the data exists in columns A and B, right-click column header "C" and select "Insert."
Type the data from the first cells of the original two columns in the first cell of the newly created column. As an example, if you had "John" in cell A1 and "Doe" in cell B1, type "John Doe" or "Doe, John" (without quotes here and throughout) in cell C1. The order and punctuation doesn't matter; Excel will automatically recognize that you're combining the data and mimic your format.
Repeat the process for the next row. This time, Excel suggests the value as you type. When you see the suggestion, press "Enter" to automatically combine the data in both columns for each row and continue using your format. As an example, if cells A2 and B2 contain "David" and "Smith," respectively, begin entering "David Smith" or "Smith, David" and press "Enter."
Click and drag your mouse across the two original column headers, right-click the selection and select "Delete" to remove the columns without affecting the data in the consolidated column.
Bye Bye, Duplicates
Click the column header to select all cells in the column.
Click the "Data" tab and then "Remove Duplicates."
Click "Expand Selection," if you also want to remove adjacent data or "Continue With the Current Selection" to only remove duplicates in the selected column. Click "Remove Duplicates." This option only appears if Excel detects data adjacent to the selected column.
Uncheck all columns except the one containing the consolidated data and click "OK" to remove duplicates. Click "OK" again to exit the summary.
- Information in this article applies to Microsoft Excel 2013. It may vary slightly or significantly with other versions or products.
- Stockbyte/Stockbyte/Getty Images