Say, for example, you had a program sign up and now have a spreadsheet with a list of people to subscribe to an email list but first you want to make sure you're not trying to subscribe the same address more than once. Sure, you could sort the list by email address then manually pick out and delete the duplicates. You could even use Conditional Formatting to highlight the duplicate entries to make them easy to find first. Or you can use the Remove Duplicates function and get rid of them in just a few clicks.
Before you do this, a word of caution. Removing the duplicates will change how many rows display. It will either shorten the column you selected making it not line up with other columns in the spreadsheet or it can delete the entire row if you have all columns selected. Depending on what you need, you may want to copy the data you want de-duplicated to another worksheet.
- Select the column where you'd like to remove the duplicates.
- Click on the Data tab
- Click on the Remove Duplicates column.
- If you have other information in the spreadsheet that's not selected, you will get a "Remove Duplicates Warning" pop-up box asking if you want to expand the selection or continue with the current selection.
- If you only want to remove data in the selected column, choose "Continue with the current selection and click on "Remove Duplicates".
- If you want to remove data from all columns, choose "Expand the selection" and click on "Remove Duplicates".
- The "Remove Duplicates" window will open. There will be a list of the columns and a checkbox in front indicating which columns you'd like to use to compare for duplicate entries.
- If you've selected a single column, there should be a checkmark in the box in front of your column (or column name if your data has headers) and you can just hit "OK".
- If you've selected multiple columns, choose which columns need to be duplicated for the entry to be removed and click "OK". For example, if you had a spreadsheet with the first name, last name and email address in separate columns and wanted to remove any entries for the same person, you'd need to have a checkmark in front of all three columns. That way only an entry with all three the same would be removed. If you just used the last name and email columns, you could delete other family members using that email address.
- Another pop-up window will appear telling you how many duplicates were found and how many items remain. Click "OK" and you'll be at your now cleaned up spreadsheet.