How to Delete Duplicates in Excel
Instead of manually searching through spreadsheets for duplicate entries, you can use one of two features in Microsoft Excel to make the job easier: the "Remove Duplicates" option or conditional formatting. The former method automatically deletes all duplicates in your spreadsheet, while the latter highlights them. If you use conditional formatting, you'll have to look through the entire spreadsheet and delete the unwanted entries by hand, but it gives you more control over the process.
Automatically Deleting Duplicates
Click and drag to select all the cells you want to check for duplicates.
Open the "Data" tab on the Ribbon and click "Remove Duplicates" in the Data Tools section.
Check "My Data Has Headers," if you included header cells in your selection.
Uncheck any columns you want to ignore. For example, if you have both "Column A" and "Column B" checked, Excel will only delete rows where the values in both columns A and B are duplicates. Alternatively, if you uncheck "Column B," Excel will delete any rows with duplicate values in column A, even if the values in B differ.
Click "OK." Excel will delete the duplicates and display a message counting how many -- but not which -- entries it removed.
Highlighting Duplicate Entries
Select all the cells you want to check for duplicates.
Open the "Home" tab and click "Conditional Formatting" in the Styles section.
Click "Highlight Cells Rules" and then "Duplicate Values."
Pick "Duplicate" from the first drop-down menu on the pop-up window. In the second menu, you can change how Excel highlights the marked cells -- the default option turns the cells red.
Click "OK." Excel will highlight duplicate cells, making it easier for you to find and remove them manually. To erase a cell's contents manually, select it and press the "Delete" key.
Click "Conditional Formatting," "Clear Rules" and then "Clear Rules from Entire Sheet" after you finish deleting all unwanted duplicates. This removes the highlighting from the remaining cells.
Tips & Warnings
- Save a backup copy of your workbook before automatically deleting duplicates, in case you later realize Excel deleted important information.
- Both methods of finding duplicates work whether your cells contain numbers or text.
- Information in this article applies to Microsoft Excel 2013 and 2010. It may vary slightly or significantly with other versions or products.