How to Count the Number of Unique Values in a List in Excel : Using Excel

Sharing buttons:

hi I'm Ted today I'm going to show you

how to count the number of unique values

in a range of cells in Excel this is

something that's very useful to do when

you have a very large data set it's a

quicker way of doing it than tediously

going through and manually counting the

individual unique values so I have a

data set here it happens to be the

members of the Baseball Hall of Fame and

what I want to do just to demonstrate

this point is to is to count the number

of unique cities these are the birth

cities of all the people in column a and

I want to focus on column G so there I'm

sure are a number of different ways of

doing this but here's a fairly foolproof

way that I want to show you the key to

it is first we want to take the column

of interest in our case the city and we

want to sort the whole data set by

column G so that they're alphabetically

sorted so we select all the columns from

A to G and we go to sort and this may

look a little different depending on

your particular operating system but

there's always a sort under data and

we're going to sort on the city and is

going to sort sure enough from A to Z

that's what we want and we have okay so

now we have the whole data set sorted by

column G the city now here's where the

trick is we want to make ourselves a

little formula in column H and the the

column is going to test whether the

value is equal to the previous value and

that will be a way of testing whether

any value is unique so what we're going

to do is we're going to say equals and

then if and then it's if this entry

which is the second city is equal to so

we type in equal the previous one comma

and then in the in parentheses if

they're equal then it's not unique so

I'm going to it's going to enter in not

unique so I'm going to type in not

unique in the quotes close the quotes

and then comma and now this is what will

happen if it's not true if it's not true

then it's unique close the quote close

the parentheses at a return

and sure enough this value is not equal

to this value and that entry therefore

is unique now of course the very first

one by itself is unique and we're just

going to manually type in unique there

then we're going to take this formula

and drag it all the way to the bottom of

the list I'm just going to go a little

ways and you'll see what happens this

one is a repeat of this one so it's not

unique this one is you repeat of this

one that's not unique so our formula is

working and actually you may know the

trick now if you have a continuous range

in column G we go to the bottom right

whoops we select all these guys in

column H and we click on the bottom

right and we double click and it copies

the formula all the way down to the end

now we're almost done here we have a now

a label in column H and you can call

that label anything you want you can

make that formula do anything you want

but we're going to now go up and we're

going to use the count if function so

just somewhere over here on the right

we're going to say equals count if open

parenthesis and now what's the range so

we select that whole range of that new

formula we just made and that's whether

it's unique or not and we whoops went a

little too far go to the bottom and then

we go up to the top so we can see what

we're doing here we're still in formula

entry mode here and over to the right

here we type a comma and then in quotes

unique it has to be spelled exactly the

way we had it in the formula in column H

close the quotes close the parentheses

type of return 203 there we go there are

two hundred and three entries in column

H that are unique so with a little bit

of work we were able to do this but if

you have a huge data sheet of thousands

and thousands of lines this will be a

very quick foolproof way of counting the

number of unique cells reminder the key

is to first sort on the cell on the

column that you want to to do these

calculations on so I hope this has been


I'm Ted and today I showed you how to

count the number of unique values in a

range of cells in Excel thank you for