If I need a list of unique items from a long list, I usually use an Advanced Filter, because it leaves the original list alone, and extracts a list of unique records.
The filter can be done in place, or sent to another range, on the same sheet, or a different sheet. It’s easier in newer versions of Excel though – here’s how to quickly remove duplicates in Excel 2013.
Advance Filter to Remove Duplicates
This screen shot shows the Advanced Filter dialog box, which lets you extract a list of unique records.
Remove Duplicates Tool
In Excel 2007, the Remove Duplicates tool was introduced, and I wrote about that a few years ago. It’s a quicker way to strip duplicates out of a list, but it works on the original list – it doesn’t create a separate list of unique items.
The tool still works the same way, in Excel 2013, and I’ve finally made a video to show how it works — it’s in the section below.
- You can use the Remove Duplicates tool on a single column list, or a list with two or more columns.
- Add check marks for the columns that you want to check for duplicates, then click OK.
And remember, make a backup before you start! If you’ve bought a copy of my Excel Tools add-in, just click the “Make Backup” button.
Video: Remove Duplicates in Excel List
To see the steps for removing duplicates from a list in Excel, you can watch this 4-minute video. Remember to make a backup of the Excel file, or the worksheet list, before you start to remove duplicates.
Note: The timeline is listed below the video.
Video Timeline
- 0:00 Excel List With Duplicate Items
- 0:21 Make Backup Copy of Excel List
- 0:42 Use Remove Duplicates Tool
- 1:24 Excel List – Two Column Duplicates
- 1:58 Select Columns
- 2:50 Excel List – Multiple Column Duplicates
- 3:42 Excel List – Multiple Column Example 2
_________________