Excel Training Home
How to use advanced filter in Excel to
extract unique records
and remove duplicates
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.
Now you can see that the unique records from your data have been
extracted and the duplicates removed.
- Click inside the data in the MS Excel Worksheet
- Click on the Data Tab
- From the command group 'sort & filter'
select 'advanced filter'
- A new window called 'Advanced Filter'
your cursor was inside your data the list range from which you wish to
extrac unique records and remove duplicates is selected for you
- You don't need to enter any criteria range
because this is not required
- In the 'Action' option select 'copy to another
location' . The 'copy to location' text box becomes active.
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
- Now click on the option 'Unique records only'
- Click OK
Watch the Excel training video below to see how this feature of
advanced filter is implemented:
advanced filter in Excel to extract unique records