How to use advanced filter in Excel to extract unique records and remove duplicates

When an operator enters data into en Excel worksheet he is bound to make errors and also enter duplicate values. The advanced filter feature in Excel along with the extract ‘unique records only’ can help us to identify the duplicate records and copy the extracted data to another location in the worksheet.

  1. Click inside the data in the MS Excel Worksheet
  2. Click on the Data Tab
  3. From the command group ‘sort & filter’ select ‘advanced filter’
  4. A new window called ‘Advanced Filter’ opens
  5. Because your cursor was inside your data the list range from which you wish to extrac unique records and remove duplicates is selected for you automatically
  6. You don’t need to enter any criteria range because this is not required
  7. In the ‘Action’ option select ‘copy to another location’ . The ‘copy to location’ text box becomes active.
  8. Click on the button to collapse the whole window and select any convenient cell where you wish to have your extracted data. Best area is to be away about 3-4 columns from the original data or 3-4 rows away
  9. Now click on the option ‘Unique records only’
  10. Click OK

Now you can see that the unique records from your data have been extracted and the duplicates removed.
Watch the Excel training video below to see how this feature of advanced filter is implemented:

Further reading:
Filter for unique values or remove duplicate values

Leave a Reply

Your email address will not be published. Required fields are marked *