How to filter a list in Excel to provide a list of unique values

Posted by on March 26, 2012 in Computer Use | 0 comments

Often you’ll have a big list that you want to filter duplicates out of. It could be because you’re copying content from a number of sources that may overlap, or you’re just rattling off a list of something as fast as you can and don’t want to go back to see if what you’re thinking of is already in the list. Either way, you’ll want a unique list of all the values. This is simple in Excel, and the tutorial below is for Microsoft Excel 2010. The steps are similar for other versions.

How to remove duplicates from a list in Excel

Start with a list of data.

unique excel data set

Highlight the set of data you want to filter, then open the ribbon and click on the Data tab.  There is a large filter button, but we want to click on the advanced button next to it.

unique excel filter column

The dialog box below comes up.  Change the first radio to Copy to another location.  Make sure the List range does not include your headers, then click in the Copy to: field.  You can either type the top cell of where you want your new list to appear, or just click on the cell while you’re in the field.  Check off the Unique records only checkbox and click OK.

unique excel filter new column

The resulting data set will show as you expected it to.

unique excel filtered set

 

Counting the occurrences of your unique list

As an additional step, you may want to know how many times each unique item showed up in the list.  This would be useful if you’re getting a raw list of data and trying to show patterns or create a graph.  Simply add another column and use the formula shown below. Place the formula in the first cell of your new column, next to the first unique value. Use the range of your full set where the formula references A1:A100, and use your unique column cell as the $B2 reference. Drag the formula down to match your list of unique values, and the result will be the count you’re looking for.

=COUNTIF(A1:A100,$B2)
 

unique excel filtered set count

 

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>