Knowing the number of unique items in a column when performing data analysis in Excel can provide you with an invaluable overview of your data. This statistic can help you quickly identify mistakes or inconsistencies since sometimes values that only appear once might be an outlier. This post will show you how to get the count of unique values in Excel. Get your copy of the example file used in this post and follow along!
Unique vs Distinct: What’s the Difference?
The term unique is often mistaken for distinct. These have very different meanings and will result in different counts. Unique values are those that only appear once in a list. The unique values from the list {A, B, B, C, C, D} are {A, D}, and the count will be 2. Distinct values are the complete set of values that are different. The distinct values from the list {A, B, B, C, C, D} are {A, B, C, D}. The count in this case will be 4. 💡 Tip: Check out this post if you are really looking for a count of distinct items.
Count Unique Values with the COUNTIFS Function
The COUNTIFS function will allow you to count the number of items in a range that satisfy a set of conditions. This can be used to count the unique items in a list. The above formula will count the number of unique items in the range B5:B14. The COUNTIFS function will return the number of times each item appears in the range. Since the unique items only appear once, they will have a count of 1. When you test if this is equal to 1, it returns an array of TRUE and FALSE values where the TRUE values will indicate the item is unique. Multiply this array by 1 and it will convert the TRUE values to 1 and the FALSE values to 0. This is summed up to get the count of unique items! 📝 Note: You might need to enter this formula using Ctrl + Shift + Enter for older versions of Excel that don’t have dynamic arrays.
Count Unique Values with the UNIQUE Function
The UNIQUE function is specifically designed to get the list of distinct or unique values from a list. This can be combined with the COUNTA function to count the number of unique values. The above formula will count the unique items in the range B5:B14. The UNIQUE function returns the list of unique values. This version of the UNIQUE function uses the second and third optional arguments which are set to FALSE and TRUE respectively.
The second argument is set to FALSE and tells the UNIQUE function to return the unique rows from the array input.The third argument is set to TRUE and tells the UNIQUE function to return items from the input array that appear exactly once.
The COUNTA function is then used to count the number of items returned from the UNIQUE function.
Count Unique Values with the Conditional Format
Conditional formatting is a great way to visually spot data that satisfies a given rule. These cells can be highlighted with a fill color. There is a conditional formatting rule that can be applied to highlight the unique values in a list. You can then filter your list based on a cell color to show only the unique values in your list. Then counting them becomes easy. You can either use a SUBTOTAL function to count only the visible cells or get the count from the status bar. You will now see all the values that only appear once are highlighted in your range. You can now filter based on these colored cells to get only the unique items in the SUBTOTAL count. 💡 Tip: You can also use the Ctrl + Shift + L keyboard shortcut to add the filter toggles to the range. You now have a count of the unique values in your data given from the SUBTOTAL function.
Count Unique Values with a Pivot Table
Pivot Tables are a great tool for summarizing large amounts of data. They allow you to aggregate fields in many ways including counting results. This means you can count the items in your list and display the count for each item. You can then use a value filter on your pivot table to filter the results down to those where the count is 1. This will show only the unique items and the grand total for the pivot table will be the found of unique items from your dataset. First, you will need to create a pivot table based on your dataset. Here’s how to add a pivot table. When you add a text field into the Values area, it will default to a Count type of aggregation. You should now see a list of all the items in the rows along with a count of these items in the values. Any item that has a value of 1 is a unique item from your data. This will filter your pivot table on only those items where the count value is 1 and therefore only shows the unique items. Now the Grand Total will show the total count of unique items!
Count Unique Values with a DAX Measure
Regular pivot tables are fairly limited with the aggregation types available for the Values area, but you can use the Data Model feature to create more advanced calculations. This way you won’t need to filter your pivot table to count the unique values, it will be done purely from a formula in the Values area of the pivot table. When you create your pivot table make sure you enable the data model. This will allow you to create measures with the DAX formula language for the Value area of your pivot table. This will open the measure editor. This creates a variable with the SUMMARIZE function. This summarizes the data by the Make column and creates an aggregated column named Unique with the calculation IF ( COUNTA ( Range[Make] ) = 1, 1, 0 ). This shows a 1 for unique items and 0 otherwise. The SUMX function is then used to iterate over the summarized virtual mySummary table variable and sums the Unique column. The pivot table will show a 1 when the field item has a unique value and 0 otherwise. The grand total will also reflect the total number of unique items.
Count Unique Values with Power Query Column Distribution
Power Query will show you preview statistics about your data in the query editor. This includes showing a count of unique items for each column in the data preview. Here’s how you can get the data into the power query editor. This opens the power query editor and you will see a preview of your data. You should also see a unique count at the top of each column. If you don’t see these column distribution statistics, then you can enable them from the View tab.
Count Unique Values with Power Query Transformation
You can also get the unique count of items in power query with a few data transformation steps. You can group your data with a summarized count to find all the items that appear a single time. You can filter the results based grouped items with a count of 1. Then when you count this filtered results, you get the count of unique items from your data. Here are the steps to group your data. This creates a new data table with each item and the corresponding count of items. Now you can filter this data. Now you can count the results. This gets you the count of unique items! You’ll get a single value here and you can load this back into Excel or use it in other queries.
Count Unique Values with VBA
Since there is now Excel function that counts unique values, one solution is to build your own. You can create a user defined function with VBA code. You’ll be able to use this like any other function in the grid and it will simplify the formula structure as compared to previous formula solutions. Go to the Developer tab and click on the Visual Basic command to open the visual basic editor. You can also press the Alt + F11 keyboard shortcut to directly open the visual basic editor if you don’t see the Developer tab in your ribbon. Now you need to create a new module to place your code. Go to the Insert tab of the visual basic editor and select the Module option from the menu. Paste the above code to the module. This uses the Application.WorksheetFunction method to access the COUNTIFS function from the worksheet and use it in the code to calculate the number of unique items. If the count of an item from the COUNTIFS function is 1, then the count of unique items uniqueCount is incremented by 1. Now you can use the above formula to count unique values in the range B5:B14.
Count Unique Values with Office Scripts
Another option to count the unique values with code is to use Office Scripts. You can create an Office Script that will allow you to select a range in the sheet and return the count of items that only appear once. Go to the Automate tab and select the New Script option. This opens the Office Script Code Editor where you can write your code. Paste the above code to the Code Editor and press the Save script button. Now you can select a range in the sheet and press the Run button in the Code Editor. The code will run and return the count in the console log output. This code loops through all the cells in your selected range and adds them into a 1 dimensional array named allItems. Then it will loop through all the items in the allItems array and check if it’s a duplicated item using the indexOf method. The indexOf method finds the array index number of the first occurrence of an item. The trick is you use this method to find the next index for the current item. If there is no next index then the indexOf returns -1 and the item is unique! Unique items are then added to another array named uniqueItems. The count of the items in the uniqueItems array is then returned in the console logs with the length method.
Conclusions
There is no specific feature in Excel for finding the number of unique items in a list. But there are many interesting ways to get this. A COUNTIFS or UNIQUE formulas solution will likely be the easiest and most dynamic option. Other features such as conditional format and filters might not be well suited. For situations involving a repeated analysis, Pivot Tables or Power Query are good choices. A custom VBA user-defined function solution can be built which makes the formula in the grid easier to implement. Office Scripts can also be used, but will either need to output to the console log or a predetermined cell in the grid. Have you implemented any of these methods for getting a unique count? Let me know how it went in the comments!
