{"id":30979,"date":"2023-09-15T22:01:30","date_gmt":"2023-09-15T20:01:30","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=30979"},"modified":"2024-03-29T21:36:03","modified_gmt":"2024-03-29T20:36:03","slug":"count-unique-values-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/count-unique-values-in-excel\/","title":{"rendered":"3 Easy Methods to Count Unique Values in Excel"},"content":{"rendered":"

\"3<\/a>
\n<\/a>
\nWhen dealing with large datasets in Excel, it becomes crucial to count the number of unique values<\/strong>. Whether you’re working with a list of customer names, product IDs, or any other type of data<\/strong>, counting unique values can provide valuable insights<\/strong>.<\/p>\n

In this article, we will cover the following three methods in detail –<\/p>\n

Let us look at each of these methods.<\/p>\n

Download the Excel Workbook below to follow along and understand How to Count Unique Values in Microsoft Excel \u2013 <\/strong>download excel workbook<\/strong><\/span>Count-Unique-Valus-in-Excel.xlsx<\/span><\/a><\/p>\n

 <\/p>\n

#1 – Filter Unique Values<\/span><\/strong><\/h3>\n

The easiest way to count unique values in Excel is by using the unique filter feature<\/strong>. Follow the steps below<\/strong> to understand how –<\/p>\n

STEP 1: <\/span><\/strong> Select the list containing the customer names.<\/p>\n

\"3<\/a><\/p>\n

STEP 2: <\/span><\/strong> Go to Data > Advanced<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 3: <\/span><\/strong> In the Advanced Filter dialog box, check unique records only<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 4: <\/span><\/strong> Select the unique values and check the count in the status bar<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

This method allows you to quickly filter and work with unique values from a dataset without modifying the original data.<\/p>\n

 <\/p>\n

#2 – UNIQUE and COUNTA Function<\/span><\/strong><\/h3>\n

This method can be useful when you want to count the number of unique values within a range or a list of data<\/strong>. The UNIQUE<\/a> function extracts the unique values<\/strong> and then the COUNTA<\/a> function counts all the non-empty cells<\/strong> in the list of unique values. Here’s how you can do it –<\/p>\n

STEP 1:<\/span><\/strong> Enter the COUNTA function.<\/p>\n

=COUNTA(<\/strong><\/p>\n

\"3<\/a><\/p>\n

STEP 2: <\/span><\/strong>Enter the UNIQUE function.<\/p>\n

=COUNTA(UNIQUE(<\/strong><\/p>\n

\"3<\/a><\/p>\n

STEP 3:<\/span><\/strong> Select the list containing customer names.<\/p>\n

=COUNTA(UNIQUE(A2:A40))<\/strong><\/p>\n

\"3<\/a><\/p>\n

The result will be the count of unique values in column A.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

#3 – Use PivotTable<\/span><\/strong><\/h3>\n

You can also count unique values in Excel using a PivotTable<\/strong><\/a>. Follow the steps below<\/strong> to understand how –<\/p>\n

STEP 1: <\/span><\/strong> Select the list containing the customer names.<\/p>\n

\"3<\/a><\/p>\n

STEP 2: <\/span><\/strong>Go to Insert > PivotTable<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 3: <\/span><\/strong>In the PivotTable dialog box, select Existing Worksheet<\/strong> and its location<\/strong>. Click OK<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 4: <\/span><\/strong>In the PivotTable Fields dialog box, drag and drop Customer Names in the Rows<\/strong> field.<\/p>\n

\"3<\/a><\/p>\n

STEP 5: <\/span><\/strong>Select the unique values and check the count in the status bar<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

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.<\/p>\n

 <\/p>\n

Conclusion<\/span><\/strong><\/h3>\n

This article discusses three methods for counting unique values in<\/a> large datasets in Excel. The first method demonstrates how to use the unique filter feature to quickly filter and work with unique values<\/strong> without altering the original data. The second method combines the UNIQUE and COUNTA functions<\/strong> to count unique values within a data range effectively. Lastly, the third method involves creating a PivotTable to count unique values<\/strong>.<\/p>\n

Further learning:<\/strong><\/p>\n