How To Count Unique Values In Microsoft Excel
One major reason Microsoft Excel spreadsheets are so popular is the ability to process the data entered on a sheet easily. For instance, if you are planning a wedding and individually listing expenses for the location, the food, the music, and everything else that goes into planning a wedding, having a function that can easily add all those values together is such a time-saver. However, adding that sum is not the only function Excel can perform when you have a column filled with inputted data.
You may have a large column containing many different words or numbers in each cell and want to know how many distinct values are in that column. For example, let's say you recently made a gigantic list of several hundred films you consider the best of all time that you will narrow to your top 100.
Obviously, you have the film titles in a column, but in your list-making, you have also created columns for the names of each film's director and release year. If you wanted to know how many different directors or distinct years were represented on that list, Excel has the capabilities that let you do that. In fact, the program actually has two different ways of accomplishing this, depending on your comfort level with Excel functions and shortcuts.
How to count unique values with a filter
If you are unfamiliar with inputting formulas into Microsoft Excel, the much simpler way to get the number of unique values in an Excel column is through a filter. Although you will end up having to use a function, it is a very simple function that is easy to grasp.
- Make sure your column has a column heading — otherwise, this method will not work.
- Select all the cells in this column that have inputted values, including the column heading.
- Select the "Data" option in the menu bar.
- In the box containing "Sort" and "Filter," select the "Advanced" option.
- Select the "Copy to another location" option.
- In the box labeled "Copy to," input a cell number from an empty column. For example, if the column you are working with starts with A1, input B1 or C1 if nothing is in those columns.
- Select the "Unique records only" option
- Click OK.
- Select the cell underneath your newly formed column.
- Input the ROWS function into that cell using the first and last cells of that column, not including the column heading.
After that, you will know how many unique values are in that column. For some of you, you could follow that until the ROWS function. Let's say your column heading is in C1, and the final value is in C10. You would type the following into cell C11:
=ROWS(C2:C10)
Now hit Enter. Unless you want your column heading counted, make sure you use the cell of the first actual value in your range.
How to count unique values with a function
If you are more comfortable with Microsoft Excel functions, you can compute these unique values through a combination of functions as well. It will take a combination of five different functions to get the number of unique values in a column. Which functions you use depends on what values are being counted. For instance, if your column is just numbers, then you can use a combination of the SUM, IF, and FREQUENCY functions. Let's say the entries in your column run from C2 to C115. Your function combo would read:
=SUM(IF(FREQUENCY(C2:C15,C2:C15)>0,1))
However, things get a little more intricate if your column contains text entries. If you were to use the above function combination for this, it would only count the rows that feature numbers. You still would use the SUM, IF, and FREQUENCY functions, but you would also add the MATCH and LEN functions. Using the same column of C2 to C15 as an example, the function would read:
=SUM(IF(FREQUENCY(IF(LEN(C2:C15)>0,MATCH(C2:C15,C2:C15,0)," "), IF(LEN(C2:C15)>0,MATCH(C2:C15,C2:C15,0)," "))>0,1))
Both function combinations will skip over any blank row and will not count towards the running total of unique values, as there is no value to be counted.