Oct 25 2009

List Unique Values from a Range

Excel has an easy built-in way to show the unique values from a list of numbers you provide. You can use it to filter a unique list of numbers, dates, strings or whatever you need to have listed.


Using the Advanced Filter to Show Unique Values

  • Below is the list I want to get the unique values from:

Unique Values Duplicates List

  • Under the Data Menu, select the Advanced Filter and the pop-up will appear.
  • Select “Copy to another location”.
  • Select the cell you want to copy the unique values to.
  • Check the box that says “Unique records only”

Unique Values Advanced Filter

  • Click OK and your sorted list will appear like below.

Unique Values Complete

That’s it.  You now have a list of all values in your range listed only one time.  It’s that easy.  I’m sure there are several places where you can use this tool to simplify your spreadsheets.

There are several other ways to achieve the same result using different tools in Excel. Excel 2007 added a button on the data tab called “remove duplicates”. You can also use a pivot-table to show unique values from a list easily.

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks