5 Ways to Count and Extract Unique Values in Excel

There are many ways you can pull and count unique values in Excel, and below, I’ll show you five ways you can do so and when you should use each method.

For this example, I’m going to use a dataset from data.gov relating to consumer complaints about financial products. It’s a large list and by listing the unique values we can see how many different complaints there are and which are the most common ones.

Using the UNIQUE function to extract values

The UNIQUE function isn’t available on all versions of Excel. You can try to type it in as a formula to see if you have access to it. If you’re able to use it then that’s great news, because this is the easiest way to pull unique values.

In my data set, I want to extract all the unique issues customers have had. I can quickly generate a unique list of values by using this function and just selecting the entire range of cells in column D. The formula is as simple as this:

=UNIQUE(D:D)

It returns an array and the unique results are now in the column where I entered the formula (E) and I’ve highlighted the list in yellow:

This gives me the entire list of unique values but it doesn’t tell me how many unique items there are. I could, of course, just use a count function to tally up all the values in column E. But there’s also another, more efficient way to do that. I can enclose the UNIQUE function within the COUNTA function that counts nonblank cells. Here’s what that formula looks like:

=COUNTA(UNIQUE(D:D))

This formula tells me that there are 167 unique values in column D. I don’t even need to first run the UNIQUE function on its own, I can do this all in one cell and quickly get the number of unique values in the list. This is by far the simplest and most elegant way to do this in Excel.

If I wanted to see the number of times a unique value appeared on the list in column D, I could use the following formula:

=COUNTIF(D:D, E2)

Where E2 is the issue that I want to count.

Let’s move on to another approach, one that’s more common in newer Excel versions.

Using the remove duplicates button

Another way that you can pull the unique values from a list is by clicking on the Remove Duplicates button located on the Data tab:

However, the caveat here is that this button will actually remove items from the existing list that aren’t unique. And so what you’ll want to do first is copy your list of values to another column, and then select that column and click on the button.

At that point, you’re back to having to count the number of duplicates in the list. A function like COUNTA could do the job. And then to tally the totals by item would involve using the COUNTIF function again.

Going old school with the advanced filter trick

If you’ve worked on older versions of excel where you didn’t have the luxury of buttons filtering out duplicate values for you, you may have used an advanced filter. How this works is you enter the header into some other adjacent range like so:

Then, click on a value in column D, and then click on the Advanced Filter button on the Data tab, which looks like this:

The advanced filter should automatically detect the range you’re looking to filter. You’ll then see a pop-up box as follows:

I’ll tick off the Copy to another location button and I’ll select F1 which is where my header in the adjacent range is located.

Ticking off the box for Unique records only is what makes the filter pick up only the unique values, which after clicking on OK will now populate column F:

The advantage of doing it this way is you don’t have to copy a list and then remove duplicates, and essentially the end result is the same. This is the method you may want to use on older versions where you don’t have access to the newer options.

There’s also one another way that you can filter out unique values, and it’s one of the more common approaches I’ve seen.

Using pivot tables

I cringe a little when I see people using pivot tables solely for the purpose of create unique lists. It’s overkill, especially given the methods listed above that do the job just fine. But it does do the job and if you’re wanting to do some sort of analysis, it’s a great way to do it all in one shot.

For example, using my data set I can quickly turn it into a pivot table and not only create a unique list but also do a count by issue and sort it from highest to lowest:

Rather than using COUNTIF, I can just let the pivot table do the counting for me. If you want to calculate the number of unique values, you can again use the COUNTA function. You can use it to count the number of values in the Row Labels section of the pivot table. You’ll of course want to adjust for any headers and grand totals to ensure you aren’t counting too many rows.

Use our FREE Add-in!

Whether you need to count unique values or create a list of them, you can easily do them through our free add-in. You don’t have to worry if you have the right version of Excel and it’ll work with the click of a button:

And that’s just one part of a much larger set of macros and buttons that can simplify your workflow:

When should you use a particular method?

There are many options to choose from here when generating a unique list of values. Here’s a quick breakdown of each method and when you’ll want to use each one:

1. The HowtoExcel.net Free Add-in. This will simplify many of your tasks beyond just populating and counting unique values.
2. The UNIQUE function. Outside of the add-in, this should be your go-to option if it’s available on your version of Excel. It’s easy and quick to not only generate a list of unique values but to also count them. The advantage of this method is that it will also auto-refresh if your data changes since it’s a formula. You won’t have to re-run the formula to get a fresh list of unique values.
3. The Remove Duplicates button. This is the next-best option to use if you aren’t able to use the UNIQUE function. It involves just the click of a button and you just need to remember to copy the list.
4. The Advanced Filter. Use this if you’re working on an old version of Excel and don’t want to install an add-in.
5. A Pivot Table. There’s no reason to use this method unless your list of values changes often and you need a quick way to refresh the list. However, if you’re also looking to analyze the data then using a pivot table could quickly jump to the top of this list as it’ll help you sort by unique values and also do calculations very quickly.

If you liked this post on how to extract and count unique values in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.