Pinterest Pixel

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

Download Excel Workbook
John Michaloudis
Posted on

Overview

3 Easy Methods to Count Unique Values in Excel | MyExcelOnline 3 Easy Methods to Count Unique Values in Excel | MyExcelOnline



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.

3 Easy Methods to Count Unique Values in Excel

STEP 2: Go to Data > Advanced.

3 Easy Methods to Count Unique Values in Excel

STEP 3: In the Advanced Filter dialog box, check unique records only.

3 Easy Methods to Count Unique Values in Excel

STEP 4: Select the unique values and check the count in the status bar.

3 Easy Methods to Count Unique Values in Excel

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(

3 Easy Methods to Count Unique Values in Excel

STEP 2: Enter the UNIQUE function.

=COUNTA(UNIQUE(

3 Easy Methods to Count Unique Values in Excel

STEP 3: Select the list containing customer names.

=COUNTA(UNIQUE(A2:A40))

3 Easy Methods to Count Unique Values in Excel

The result will be the count of unique values in column A.

3 Easy Methods to Count Unique Values in Excel

 

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

3 Easy Methods to Count Unique Values in Excel

STEP 2: Go to Insert > PivotTable.

3 Easy Methods to Count Unique Values in Excel

STEP 3: In the PivotTable dialog box, select Existing Worksheet and its location. Click OK.

3 Easy Methods to Count Unique Values in Excel

STEP 4: In the PivotTable Fields dialog box, drag and drop Customer Names in the Rows field.

3 Easy Methods to Count Unique Values in Excel

STEP 5: Select the unique values and check the count in the status bar.

3 Easy Methods to Count Unique Values in Excel

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:

Click here to explore more about counting unique values in Excel.

If you like this Excel tip, please share it
3 Easy Methods to Count Unique Values in Excel | MyExcelOnline 3 Easy Methods to Count Unique Values in Excel | MyExcelOnline
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

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...