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

Next Video:
How Do I Create a Graph With Lines & Bars Together in Excel 2007?....5

Counting the number of unique values in a range in Excel is especially useful if you have a very large data set. Count the number of unique values in a range in Excel with help from a mechanical engineer with 32 years of experience in a large aerospace company in this free video clip.

Part of the Video Series: Using Excel
Promoted By Zergnet


Video Transcript

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 then 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 fool-proof way that I want to show you. The key to it is first we want to take the column of interest, and 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 the 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 it's 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 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 gonna 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 parenthesis, if they're equal, then it's not unique, so I'm gonna, it's gonna enter in not unique, so I'm gonna 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 parenthesis, enter 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 gonna 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 gonna 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 a repeat of this one, and 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, oops, 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 gonna say equals count if, open parenthesis, and now it wants 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 parenthesis, type return, 203. There we go, there are 203 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 would be a very quick, fool-proof way of counting the number of unique cells. A reminder, the key is to first sort on the cell on the column that you want to do these calculations on. So I hope this has been helpful. 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 watching.


Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!