Unique filter in Excel – the manual 'one-off' options

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

There are two a simple ways to make a list of unique or non-duplicate entries from a longer list.  Unlike the many automatic ‘unique’ options in Excel, these options work only once but it’s available in many Excel release, old and new.

Advanced Filter

One is hiding at Data | Sort & Filter | Advanced

Click on Advanced to see the Advanced Filter dialog.  It has an option to filter the selected list directly.  More commonly ‘Copy to another location’ keeps the original list untouched and makes a copy with only selected items.

List Range: the source list.  Like many Excel dialogs, it’s faster to select the range before opening the dialog.

Criteria Range:  a filter to apply, if any.

Copy to:  where to put the results, after filtering.

Unique Records only

That little check box at the bottom is all you need to copy a list of unique no-duplicates record.

Copies this list of dwarves.

You could add a filter criteria as well. For example criteria to show names with sales above a certain value AND unique records.

Remove Duplicates

The other de-duplication option is nearby at  Data | Data Tools | Remove Duplicates.

Remove Duplicates works by trimming the selected list, so best to make a copy of the table or cells first.

Select the table/cells then Data| Remove Duplicates.  Choose the column with the duplicates.

The result is a shorter list with no duplicates.

If you’re sure that a ‘one-off’ copy is enough, use Advanced Filter or Remove Duplicates.  Overall it’s better and safer to use a method which is automatically updated when the source list changes.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address