Counting unique values in Excel 2010

Let’s say you have a column of values, and you want to know what the unique values are and how MANY of each value there are.  There isn’t a really straightforward way to do this, but you can do it with a couple of easy steps.  Here’s our sample data:


What we do is to select those cells and make a copy below:


Now, select those cells and click “remove duplicates” (in the “Data” tool ribbon):


Just click OK, and you’ll see this:


So now your spreadsheet looks like this:


In cell B18, paste this formula: =COUNTIF(A$1:A$15,A18)


Now, grab the “handle” at the lower right corner of that cell and drag it down to B21.  This copies the formula to the cells below.


SO, the left hand column now contains the unique values, and the right hand column tells you how many of each you have.

By inserting a pie chart, and picking A18-A21 as the legends, and B18-B21 as the data, you get this:



2 comments on “Counting unique values in Excel 2010

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s