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:

image

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

image

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

image

Just click OK, and you’ll see this:

image

So now your spreadsheet looks like this:

image

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

image

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.

image

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:

image

Advertisements

2 comments on “Counting unique values in Excel 2010

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s