How to count unique values In Excel – 5 easy methods (original) (raw)

You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

If you want to learn how to count unique values in Excel, you’ve come to the right place.

Any kind of spreadsheet will contain both unique and distinct values. Unique values only appear once in a list, while distinct values are all the values that appear within a list. So, for example, if you had a list of numbers containing the values ‘1, 4, 7, 5, 7, 4’, then the unique values would be ‘1 and 5’ while the distinct values would be ‘1, 4, 7, and 5.’

AMD Ryzen 7 9800X3D returns to Amazon

AMD's Ryzen 7 9800X3D is finally back in stock on Amazon.US. Remember, use the "other sellers" button to ensure you get the best price through Amazon.

Prices correct as of November 14th, 2024.

When working in Excel, there may be occasions where you will need to identify how many unique values are contained within a list. So, to help you efficiently sort through the data, we put together this guide with 5 easy methods to teach you how to count unique values in Excel using various functions.

How to find out unique values from a dataset in Excel

Scenario on hand: We have a dataset with customer names who availed services at a beauty salon.

What we want to accomplish: Explore how to return values that are unique or distinct in the dataset using the following ways:

Counting unique values with COUNTIF

COUNTIF is a function in Excel that allows you to count values with a defined condition. Here are the steps to follow to use this method to count unique values in your dataset:

Step 1: Prepare the dataset

The first step is to prepare the dataset for the function. This formula returns the number of values that occur only once in the table. So, if you have data scattered across different sheets, consolidate it into one table.

For our demonstration, we have a dataset with customer names and dates on which they availed services at a beauty salon:

A screenshot of an Excel spreadsheet with a table titled "dataset for counting unique customers," highlighting columns for customer name, service type, and date, with an additional note on the right saying "Count of

Step 2: Enter the formula

In the cell where you want to get the number of unique values, type this COUNTIF formula:

=SUM(IF(COUNTIF({Column array, Column array})=1,1,0))

For our dataset, the formula looks like this:

=SUM(IF(COUNTIF(B6:B14,B6:B14)=1,1,0))

Here’s what this formula returns:

A screenshot of an Excel spreadsheet with data organized in columns from A to E, where column A lists customer names, column B the service type, and column D the date visited. Cell E2 counts

Note that this formula only gives us the count of the dataset’s unique values, not the unique ones. It also doesn’t differentiate between numeric and text values.

In the following method, we enter a formula that finds the number of unique text values and unique numeric values in the list.

Counting unique text and numeric values

This method lets you count the unique text and numeric values separately.

Step 1: Counting unique text values

You can add another function to the formula we used in the first method to search for a specific kind of unique value.

Let’s suppose that our dataset has two months of data. Suppose that we include numbers in place of customer names for those who have a membership card.

We want to ask whether customers with a membership card have availed beauty salon services once in two months to send them a special discount.

To search for these unique numerical values, here’s the altered formula we use:

=SUM(IF(ISNUMBER(data)*COUNTIF(data, data)=1,1,0))

For our dataset, we enter this formula:

=SUM(IF(ISNUMBER(B6:B14)*COUNTIF(B6:B14, B6:B14)=1,1,0))

Here’s what we get:

Screenshot of an Excel spreadsheet with a formula typed into a cell, showcasing how to count unique values from a table column.

Step 2: Counting unique text values

Let’s try the text formula for getting the number of unique text values in our list:

=SUM(IF(ISTEXT(data)*COUNTIF(data, data)=1,1,0))

=SUM(IF(ISTEXT(B6:B14)*COUNTIF(B6:B14, B6:B14)=1,1,0))

Here’s the result:

The image illustrates how to excel in using a spreadsheet application, displaying a table that tracks customer names, services, and unique dates. It uniquely highlights the count of unique values for one-time non-members,

These formulas will count how many unique values are present in your list, but they won’t tell you what these unique values are.

Using the UNIQUE function

The formulas in the first two methods only count how many unique values are present in your list but won’t tell you what these unique values are.

The unique function is a simple way to get a list of the unique values.

Step 1: Use the UNIQUE formula

Simply write this formula for your data:

=UNIQUE({Column array})

=UNIQUE(B6:B14)

Here’s how it works for our dataset:

A How To screenshot of an Excel window with a list of customer names and corresponding service types, highlighting a cell where a formula is being used to count unique values in a designated range.

Note: This formula will create an array. If you attempt to change any cell in this array, it will show a #SPILL error:

A screenshot of a Microsoft Excel spreadsheet displaying a partially filled table with instructions on how to count unique values. There is a highlighted error message "#spill!" indicating an issue with an array formula in the

Step 2: Adding TRUE to the UNIQUE function

The UNIQUE function returns all the distinct values in the list. This means we have a list of all the customers who availed services, regardless of how many times.

To get this formula to return only those unique values that occur once in the dataset, here’s the modified formula to use:

=UNIQUE({Column array},,TRUE)

=UNIQUE(B6:B14,,TRUE)

Here’s the result:

An Excel spreadsheet is displayed with cells filled with various data, including a column for customer name, service type, date visited, and a highlighted column titled "How To Count Unique Values", showcasing the method

Note: This formula has two commas. If you enter just a single comma in the formula, it will return all the values in the column as it is:

A screenshot of a Microsoft Excel spreadsheet focused on tracking customer service types and unique customers, with the 'Count Unique Values' function highlighted to filter one-time customers.

Step 3: Count the unique values

The simple UNIQUE function returns a list of the distinct entries in the dataset. But if you only wish to get a number, there’s a way to count using this function.

Here’s the modified formula for this purpose:

=COUNTA(UNIQUE({Column array}))

=COUNTA(UNIQUE(B6:B14))

The image depicts a computer screen with an open Microsoft Excel spreadsheet, showing a table with columns for customer name, service type, and date. A formula in cell D14 to count unique values is "=

Additionally, by adding TRUE in the function, you can get the number of unique values in the list – the values that occur only once.

Here’s the formula:

=COUNTA(UNIQUE({Column array},,TRUE))

=COUNTA(UNIQUE(B6:B14,,TRUE))

Here’s the result:

A screenshot of an Excel spreadsheet with a focus on customer service data, where a formula in cell E2 is being used to count unique values from one-time customers.

Using the Advanced Filter option

The Advanced Filter option is the fourth way to get a list of distinct records from your table.

Step 1: Choose the Advanced Filter

The first step is to head to the Data tab from the top ribbon and locate ‘Advanced’ under the ‘Sort & Filter’ options.

A screenshot of an Excel spreadsheet with cells containing names and service types, highlighting a function used to count unique values in a specific data set.

Step 2: Enter the details

In the window that pops up, select your column. Then, choose another location to copy the filtered table.

Check the option ‘Unique records only.’

A screenshot of a Microsoft Excel spreadsheet with a 'remove duplicates' dialogue box open, highlighting the process of deduplicating data in a column labeled 'customer name', and providing a count of unique values

Here’s the result:

How To: Screenshot of an Excel spreadsheet displaying a list of customer names and services with one cell highlighted in yellow and arrows pointing from a duplicate entry to a list where duplicates have been excluded to count

Using the filter for unique values

A simple trick to see the number of unique values in your table is to apply the filter.

Step 1: Apply a filter on your table

Click on any cell on your table. Then head to Data > Filter (under the ‘Sort & Filter’ options)

A screenshot of a Microsoft Excel spreadsheet with a section highlighted in yellow and a mouse pointer hovering over the 'sort ascending' button on the toolbar. The spreadsheet appears to be tracking customer names, service.

Step 2: Click the filter icon

Clicking this Filter option will add small arrows on the top label row. You can click the triangle icon on your desired column to see the unique records in your table.

A screenshot of an Excel application with an open spreadsheet showing a list of customer names and dates, with one cell highlighted in yellow and the drop-down filter menu activated for the "date" column displaying unique

You can then select individual customer names and check how many times they are listed on the table.

For instance, we want to see how often customer ‘Alice Smith’ has visited the salon.

A screenshot of a Microsoft Excel application with a spreadsheet open showing how to apply a filter to a column labeled "values" where the number "11-jan" is selected from a drop-down menu.

We only select ‘Alice Smith’ from the list, then click ‘OK.’

We see that customer ‘Alice’ visited three times in the month.

A screenshot of a Microsoft Excel spreadsheet with a partial view of two worksheets named 'How To Count Unique Values' and 'customers'. The active worksheet displays a table with column headers 'customer name

Frequently asked questions

What is the difference between unique and distinct values in Excel?

In Excel, unique values refer to values that occur just once in the data. On the other hand, distinct values are all the values listed in the table, regardless of the number of times they are repeated.

Wrapping up

The five methods mentioned in this guide are some ways to count the number of unique or distinct values within a given list. Combining these tricks with other functions will allow you to see exactly what the unique values in your data set are, as well as how many of them are present. This is also very helpful for companies and individuals looking for anomalous data or trying to understand recent trends across a data set.

If you’re want to expand your Excel knowledge, check out some of in-depth guides below.