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: 


2 Comments on this post


  1. Automobile India said:

    I’m trying to find a maximum value for the horizontal range of a function in excel, but, I need it to be specified that the value in the next column over (for the vertical displacement) is greater than or equal to 0.

    December 2nd, 2009 at 6:00 am
  2. Automobile India said:

    This is an array formula. So when you enter the formula, press Ctrl+Shift+Enter instead of just Enter.When there are no more unique values to return, the formula will return #N/A. To get rid of those, use IFERROR function if you use 2007. Any other version, use ISERROR .

    February 1st, 2010 at 11:29 am


You must be logged in to post a comment.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks