Skip to content

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

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 releases, 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 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.

Unique() makes a once difficult task really easy in Excel
Extend UNIQUE() for distinct values that appear more than once

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.