Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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.
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.
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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.