Datasets in Excel often contain the same value multiple times in a column. Sometimes, it can be useful to know how many unique values are in a column. For example, if you run a store and have a spreadsheet of all your transactions, you might want to determine how many unique customers you have, rather than counting every single transaction.
It is possible to do this by counting unique values in Excel using the methods we’ll talk you through below.
Remove Duplicate Data From a Column
A quick and dirty way to count the unique values in Excel is to remove the duplicates and see how many entries are left. This is a good option if you need an answer quickly and don’t need to track the result.
Copy the data into a new sheet (so you don’t accidentally delete any data you need). Select the values or column that you want to remove the duplicate values from. In the Data Tools section of the Data tab select Remove Duplicates. This removes all the duplicate data and only leaves the unique values.
The same process works if the information is split between two columns. The difference is that you need to select both columns. In our example, we have a column for the first name and a second for the last name.
If you want to track the number of unique values, you’re better off writing a formula instead. We’ll show you how to do that below.
Count Unique Values with an Excel Formula
To count only unique values we have to combine several Excel functions. First, we need to check if each value is a duplicate, then we need to count the remaining entries. We also need to use an array function.
If you’re just looking for the answer, use this formula, replacing each instance of A2:A13 with the cells you want to use:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}
How we got there is a little complicated. So if you want to understand why that formula works, we’ll break it down one piece at a time below.
Explaining an Array Function
Let’s first start by explaining what an array is. An array is a single variable that holds multiple values. It’s like referring to a bunch of Excel cells at once instead of referring to each cell individually.
This is a weird distinction from our point of view. If we tell a formula to look at cells A2:A13 normally or as an array, the data looks the same to us. The difference is in how Excel treats the data behind the scenes. It’s such a subtle difference that the newest versions of Excel don’t even distinguish between them anymore, though older versions do.
For our purposes, it’s more important to know how we can use arrays. If you have the newest version of Excel, it automatically stores data as an array when it’s more efficient to do so. If you have an older version, when you finish writing your formula, press Ctrl + Shift + Enter. Once you do, the formula will be surrounded by curly brackets to show it is in array mode.
Introducing the FREQUENCY Function
The FREQUENCY function tells us how many times a number appears in a list. This is great if you’re working with numbers, but our list is text. To use this function, we have to find a way to convert our text to numbers first.
If you’re trying to count the unique values in a list of numbers, you can skip the next step.
Using the MATCH Function
The MATCH function returns the position of the first occurrence of a value. We can use this to convert our list of names to number values. It needs to know three pieces of information:
- What value are you looking for?
- What data set are you checking?
- Are you looking for values higher, lower, or equal to the target value?
In our example, we want to look up each name of our customers in our Exel spreadsheet to see if their exact name appears again elsewhere.
In the example above, we’re searching our list (A2:A13) for Tiah Gallagher (A2) and we want an exact match. The 0 in the last field specifies that it must be an exact match. Our result tells us where in the list the name appeared first. In this case, it was the first name, so the result is 1.
The problem with this is that we’re interested in all our customers, not just Tiah. But, if we try to search for A2:A13 instead of just A2, we get an error. This is where array functions are handy. The first parameter can only take one variable or else it returns an error. But, arrays are treated like they are a single variable.
Now our function tells Excel to check for matches for our entire array. But wait, our result hasn’t changed! It still says 1. What’s going on here?
Our function is returning an array. It goes through each item in our array and checks for matches. The results of all the names are saved in an array, which is returned as the result. Because a cell only shows one variable at a time, it’s showing the first value in the array.
You can check this for yourself. If you change the first range to A3:A13, the result will change to 2. This is because Eiliyah’s name is second in the list and this value is saved first in the array now. If you change the first range to A7:A13, you get 1 again because Tiah’s name first appears in the first position of the data set we’re checking.
Using the FREQUENCY Function
Now that we’ve changed the names to number values, we can use the FREQUENCY function. Similar to MATCH, it requires a target to look for and a data set to check. Also similarly to MATCH, we don’t want to look for just one value, we want the function to check each item in our list.
The target we want the FREQUENCY function to check is each item in the array that our MATCH function returned. And we want to check the data set returned by the MATCH function. Thus, we send the MATCH function we crafted above for both parameters.
If you’re looking for unique numbers and skipped the previous step, you would send the range of numbers as both parameters. To search all of the numbers in your list, you will need to use an array function too, so remember to press Ctrl + Shift + Enter after you entered the formula if you’re using an older version of Excel.
Now our result is 2. Again, our function is returning an array. It is returning an array of the number of times each unique value appeared. The cell is showing the first value in the array. In this case, Tiah’s name appears twice, so the frequency returned is 2.
Using the IF Function
Now our array has the same number of values as we have unique values. But we aren’t quite done. We need a way to add this up. If we convert all the values in the array to 1, and sum them, then we’ll finally know how many unique values we have.
We can create an IF function that changes all values above zero to 1. Then all the values will equal 1.
To do this, we want our IF function to check if the values in our FREQUENCY array are greater than zero. If true, it should return the value 1. You will notice that now the first value in the array returns as one.
Using the SUM Function
We are in the final stretch! The last step is to SUM the array.
Wrap the previous function in a SUM function. Finished! So our final formula is:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}
Counting Unique Entries in Excel
This is an advanced function that requires a lot of knowledge about Excel. It can be intimidating to try. But, once it’s set up, it can be very helpful, so it might be worth working through our explanation to make sure you understand it.
If you don’t need to count unique entries that often, the quick and dirty tip of removing duplicate values will work in a pinch!