Download Excel Workbook
Watch our free training video to Count Unique Values in Microsoft Excel:
When dealing with large datasets in Excel, it becomes crucial to count the number of unique values. Whether you’re working with a list of customer names, product IDs, or any other type of data, counting unique values can provide valuable insights.
In this article, we will cover the following three methods in detail –
Let us look at each of these methods.
Download the Excel Workbook below to follow along and understand How to Count Unique Values in Microsoft Excel – download excel workbookCount-Unique-Valus-in-Excel.xlsx
#1 – Filter Unique Values
The easiest way to count unique values in Excel is by using the unique filter feature. Follow the steps below to understand how –
STEP 1: Select the list containing the customer names.
STEP 2: Go to Data > Advanced.
STEP 3: In the Advanced Filter dialog box, check unique records only.
STEP 4: Select the unique values and check the count in the status bar.
This method allows you to quickly filter and work with unique values from a dataset without modifying the original data.
#2 – UNIQUE and COUNTA Function
This method can be useful when you want to count the number of unique values within a range or a list of data. The UNIQUE function extracts the unique values and then the COUNTA function counts all the non-empty cells in the list of unique values. Here’s how you can do it –
STEP 1: Enter the COUNTA function.
=COUNTA(
STEP 2: Enter the UNIQUE function.
=COUNTA(UNIQUE(
STEP 3: Select the list containing customer names.
=COUNTA(UNIQUE(A2:A40))
The result will be the count of unique values in column A.
#3 – Use PivotTable
You can also count unique values in Excel using a PivotTable. Follow the steps below to understand how –
STEP 1: Select the list containing the customer names.
STEP 2: Go to Insert > PivotTable.
STEP 3: In the PivotTable dialog box, select Existing Worksheet and its location. Click OK.
STEP 4: In the PivotTable Fields dialog box, drag and drop Customer Names in the Rows field.
STEP 5: Select the unique values and check the count in the status bar.
This method is particularly handy when you need a summarized view of unique values within your dataset or when you want to analyze data in a more structured manner.
Conclusion
This article discusses three methods for counting unique values in large datasets in Excel. The first method demonstrates how to use the unique filter feature to quickly filter and work with unique values without altering the original data. The second method combines the UNIQUE and COUNTA functions to count unique values within a data range effectively. Lastly, the third method involves creating a PivotTable to count unique values.
Further learning:
- How to Highlight Unique Values in Excel
- Highlight Unique Values in Selection Using Macros In Excel
- UNIQUE Formula in Excel
Click here to explore more about counting unique values in Excel.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
