How to extract/list Unique values in a column in Microsoft Excel

By Winsockfix | Comments (1) | Trackbacks (0) | Email This Post Email This Post

If you're new here, you may want to subscribe to Windows Reference RSS feed Thanks for visiting!

There are times when you have hundreds of thousands of values in a column in an Excel sheet or a CSV file with more than one occurence and you will need to extract a list of unique values from it. Sounds like a daunting task??? Not really, with a few clicks you can extract unique values from a column in an Excel sheet.

In Excel 2007

1. Click the Data tab and click Advanced under Sort & Filter
excel1
2. In the popup window, select “Copy to another location”.

3. Click button for “List Range” and select the column or all the rows in the column from which you want to extract unique values.

NOTE: The 1st Row is considered heading and hence if you have a value in another row in the same column then this will show up twice (1st row again a heading)

excel2
4. Click button for “Copy to” and click on a cell to which you want to extract the unique values to.

5. Select “Unique records only” and click OK. This should extract unique values to the column.

excel3

In Excel 2003 & earlier version, Select Advanced filter from Data Menu item and follow the above instructions to extract unique values.

  • Share/Bookmark

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.

Tags: , , , ,
Topics: MS Office

Trackbacks & Pingbacks

No trackbacks/pingbacks yet.

Comments

Just what I was looking for…

Comment by Saubz on April 2, 2009 @ 7:54 am

Leave a comment

Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

(required)

(required)