3 Easy Methods to Count Unique Values in 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.