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

unique filter in excel the manual one off option microsoft excel 30610 - Unique filter in Excel – the manual 'one-off' options

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.

unique filter in excel the manual one off option microsoft excel 30612 - Unique filter in Excel – the manual 'one-off' options

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.

unique filter in excel the manual one off option microsoft excel 30613 - Unique filter in Excel – the manual 'one-off' options

Copies this list of dwarves.

unique filter in excel the manual one off option microsoft excel 30614 - Unique filter in Excel – the manual 'one-off' options

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.

unique filter in excel the manual one off options microsoft office 30623 - Unique filter in Excel – the manual 'one-off' options

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.

unique filter in excel the manual one off options microsoft office 30624 - Unique filter in Excel – the manual 'one-off' options

The result is a shorter list with no duplicates.

unique filter in excel the manual one off options microsoft office 30625 - Unique filter in Excel – the manual 'one-off' options

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.

subs profile e1563205311409 - Unique filter in Excel – the manual 'one-off' options
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