# 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.... read more

## Overview

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.

### #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:

##### John Michaloudis
Founder & Chief Inspirational Officer at

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 MyExcelOnline Academy Online Course.

See also  Data Cleansing Training - Clean & Extract Data Using Formulas & Excel's Analytical Tools