The tool "Remove Duplicates"
It is very easy in Excel to extract all unique values from a list of data using the tool Remove Duplicates.
This tool exists in any version of Excel. Since the Excel 2007 version, the menu is directly accessible from the ribbon. But for versions prior to Excel 2003, the tool is difficult to find.
But in some cases, the tool is not working. Not because there is a bug but because the data are too complex. We will see this example in the following article. It is also possible to create an extraction without duplicates with an array formula (for the experts only).
Excel 2007 and after
To extraction without duplicates, you have to follow this 2 steps
- Select the column where want to remove the duplicates
- Click on the tool Remove Duplicates
In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be include in the process to remove duplicates.
Click on OK. The process to remove the duplicates starts immediately.
At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values remains.
Remove on multi-columns
This tool is really impressive because it works also if you select more than 1 column.
- Select your 2 columns
- Activate the tool Remove duplicates
- Check the option My data has headers
- Click OK
The result is:
Excel 2003 and earlier
Before Excel 2007, it was also possible to remove duplicates but the tool was not easy to find. The tool is in the menu Data>Filters>Advanced filter
In addition, you can specify if you want to perform the process to remove duplicates on the same place or in another location.
- Start by activating the Data menu>Filters>Advanced Filter
- Specify the cell range that will be filtered.
- Choose the Copy to another location menu and select a destination area where your list will be displayed without duplicating.
- Finally, check the box Unique records only.