Excel Unique Values

Get FREE Advanced Excel Exercises with Solutions!

In this article, we have demonstrated all the necessary information that is related to Excel unique values. We will use functions and pivot table to demonstrate how to find unique values. Furthermore, learn all the ways to use the UNIQUE function to find unique values in Excel.

Unique values are crucial in all professions. Starting from data management and analysis to the decision-making process, unique values offer deeper insights, important information, trends, patterns, and diversity. Therefore, in Excel, professionals often require different kinds of analysis or financial modeling depending on various purposes. We hope you can take away all the information and be able to use it in your work for better and more efficient outcomes.

overview image of Excel unique values

How to Find Unique Values in Excel: 3 Suitable Ways

In Excel, there are different ways to find unique values, depending on the purpose and the dataset. You can use a toolbox, pivot table, Excel function, or other Excel features as per your preference.

1. Using Sort & Filter Group

Assume that we have the following dataset that contains the names of the products and their sales date. We will use this dataset to find unique values in Excel.

Start by selecting the range of data and navigating to the Data tab. In the Sort & Filter group, choose the Advanced option. (Click on the image for clear visualization)

how to navigate the advanced filter option

Within the Advanced Filter window, you have two choices. You can either filter the data within the existing range or copy it to a different location. In this case, we will filter within the existing range.

advanced filter window

Make sure to select the “Unique records only” checkbox to filter for unique values. Finally, click the OK button to apply the filter.

final output from using sort and filter group

2. Using Pivot Table Fields

Another method to find and count unique values in Excel is by using a pivot table. We will use the previous dataset to demonstrate the process.

Select the range of data and then go to the Insert tab in Excel. From the drop-down menu of the PivotTable option, select From Table/Range.

inserting pivot table in Excel

A new window will appear. Choose the location for the pivot table, such as the existing worksheet where the location is cell G5. Finally, click OK to create the pivot table.

pivot table window

On the left side of the window, you will see the PivotTable Fields section. Select the Product Name and drag it to both the Rows and Values field boxes.

using pivot table fields options

The pivot table will generate the output, showing the total count of unique products and the number of units sold for each product.

final output from using pivot table

3. Find Unique Values by Removing Duplicate Values

We can find unique values in Excel by removing duplicate values. We can use the built-in “Remove Duplicates” feature. Once again using the same dataset, we will demonstrate the process.

Select the data range and go to the Data tab in Excel. In the Data Tool group, click on the Remove Duplicates button.

finding remove duplicates option in Excel

From the Remove Duplicate dialogue box, select the column that has duplicate values, such as the Product Name column. Make sure the “My data has headers” option is checked if your range includes headers. Click OK to continue.

remove duplicate window

It will show the following notification after removing all the duplicates.

notification window

We will have the following output, where we will find all the unique products.

final output by removing duplicate values

How to Use UNIQUE Function to Find Unique Values for Multiple Criteria: 12 Useful Methods

In the updated versions of Excel, such as Excel 365, the UNIQUE function is available to find unique values in a dataset. Besides, The UNIQUE function offers various uses and can be applied in multiple ways. Check out the following section to learn all the ways to use the UNIQUE function in Excel.

Method 1: Find Unique Values

We will use the following dataset to find the unique products by using the UNIQUE function.

First, select cell E5 under the Unique Products column and type the following formula.


Then press ENTER and you will see that it will return a list of all the unique products available in the dataset.

final output from the UNIQUE function

NOTE: To avoid the #SPILL error, provide adequate space both at the bottom and left side of the UNIQUE function.

Read More: How to Get Unique Values in Excel

Method 2: Combining SORT & UNIQUE Functions

We can sort the output result of the UNIQUE function with the help of the SORT function. Once again, we will use the same dataset to demonstrate the process.

First, select cell E5, and then we will use the following formula.


Finally, pressing ENTER will provide the following output.

using a combination of SORT and UNIQUE functions

As observed, the SORT function takes the values returned by the UNIQUE function and arranges them in sorted order, resulting in the updated output.

difference between sort and unsorted unique values

Method 3: Using UNIQUE Function to Concatenate Unique Values

We will use the following dataset to show how to use the UNIQUE function to merge or concatenate texts in Excel.

First, we will select cell E5 and then apply the following formula.

=UNIQUE(B5:B14&" "&C5:C14)

After pressing ENTER, the formula will generate the output as shown in the image. The previously separate columns for first name and last name are now combined to form the full name.

result after concatenating unique values

If there are repetitions in the names, the formula will ignore the names that appear more than once in the data, for example, Ava Davis. It will only consider unique names when combining them into the full name.

Method 4: Find Unique Values by Comparing Two Columns

We can also use the UNIQUE function to find unique values in two or more columns. Check out the following example.

Assume that in the following dataset, there are two columns containing the names of the regions and the distributors‘ names. We are going to find the regions and unique distributors in each region.

Once again, select cell G5 and use the following formula.


It will return the following output. You will see that it lists the regions and, beside them, the unique distributor names.

final output from comparing two columns

Read More: Create List of Unique Values from Multiple Sheets

Method 5: Find Unique Values Along Rows

Consider that we have the following dataset that contains the hourly sales time and the device name that was sold within the observation week. We are going to use this dataset to demonstrate how to find unique values in rows.

First, select the cell in C18 and use the following formula.


The second argument in the UNIQUE function is optional. If the by_col parameter is set to TRUE it will consider each column to be separated and return unique values for each column.

unique values along rows

Thus, it returned the unique products that were sold on May 1, 2023, which are the iPhone 8 and iPhone 12. These two products were the only ones sold throughout the entire day of work.

Method 6: Find Unique Values That Appeared Exactly Once

Assume that we have the following dataset, and we want to find the devices that were sold only once within this sample dataset.

Start by selecting cell E5, and we will use the following formula.


Here, the third argument, exactly_once in the UNIQUE function is also optional. If this is set to TRUE it will return the unique values that appeared in the dataset just once.

finding unique values that appeared exactly once

By checking the output, it becomes evident that there are only two specific phone models, namely the iPhone 8 and the iPhone 12 Mini, that were sold only once within the observed time period.

Read More: VBA to Get Unique Values from Column into Array

Method 7: Combining UNIQUE, FILTER, and COUNTIF Functions to Find Distinct Values with Multiple Entries

We can also find the values that occur more than once in a dataset. Using the same data, we will demonstrate the process.

First, select cell E5, and then we will use the following formula.


Finally, pressing ENTER will provide the following output.

find distinct values with multiple entries

The formula finds and returns a list of unique values from the range B5:B14 that appear more than once in the range.

Formula Breakdown:
  • The COUNTIF function compares each value in the B5:B14 cell range and finds values that appear more than once (greater than 1).
  • The FILTER function selects the values that have a greater count than 1.
  • Finally, the UNIQUE function returns the list of values that appear more than once by removing the duplicates.

Method 8: Find Unique Values for Single Criteria

We can also find unique values based on different criteria. For example, assume that we use the following dataset to find the products in a specific category, such as Electronics.

Once again, we will select cell H5 and use the following formula.


Finality, press ENTER to get the result.

final output of unique values single criteria

Here, the FILTER function filters the data range based on the criteria of whether it is equal to G5 or not, and in the end, the UNIQUE function returns the unique values, eliminating any duplicates.

Read More: Copy Unique Values to Another Worksheet in Excel

Method 9: Extract Unique Values with Multiple Criteria

We can also find unique values based on multiple criteria. We will use the following dataset to find the electronics products that have a price above $140 and a quantity less than 8.

dataset for extract unique values with multiple criteria

Begin by selecting cell H5 and using the following formula.


Finality, press ENTER to get the result.

final output from unique values multiple criteria

Multiple criteria are used within the FILTER function for filtering the data, and the UNIQUE function then returns the unique values from multiple criteria.

As a result, we have one product (Product C) that satisfies both criteria.

Formula Breakdown:
  • The criteria (C5:C14=G5) checks for equality between the range C5:C14 and the value in cell G5. While (D5:D14>G14)+(E5:E14>G15) checks if the values in the ranges D5:D14 or E5:E14 are greater than the values in cells G14 or G15.
  • These combined conditions as a filter in the FILTER function select the matching rows.
  • Finally, the UNIQUE function finds and returns only the unique rows from the filtered range.

Method 10: Extract Unique Values with OR Criteria

We can also find unique values based on multiple OR criteria. For example, we are going to find unique products that are from the electronics and accessories category.

Once again, select cell H5 and use the following formula.


Finally, press ENTER to find the output.

result from unique values or criteria

Thus, we find three products from two different categories that match the OR criteria.

Method 11: Using COUNTA Function to Count Unique Values

If you want to count the number of unique values in the dataset, you can use the following formula.

For example, we used this dataset to count the total number of unique products. In cell E5, we used the following formula.


With the help of the COUNTA function, it returned the total number of unique values found in the dataset, which is 6.

using COUNTA function to count unique values

Read More: Extract Unique Values Based on Criteria

Method 12: Find Unique Values in Excel by Ignoring Blank Cells

If your Excel dataset has blank cells, empty spaces, or zero values, you can use the following formula to avoid such information.

In cell D17, we used the following formula to avoid blank spaces and empty cells.


find unique value by ignoring blank cells

As a result, the formula returns an output that does not have any blank space or a zero value.

NOTE: This formula will count zero (0) or empty strings. Use the FILTER function to filter zeros or empty strings.

How to Handle Error Values While Looking for Unique Values

To handle situations where there are no unique values in a dataset and prevent error values, you can use the IFERROR function. This allows for smoother data analysis and prevents errors by providing a clear output.

error in unique function formula

In our attempt to identify unique electronic products priced above $140 and with a quantity greater than 8, it was found that there are no products meeting these criteria. Therefore, instead of displaying an error message (#CALC!), the formula returns the message “No Unique Values“.

=IFERROR(UNIQUE(FILTER(B5:C14,(C5:C14=G5)*(D5:D14>G14)*(E5:E14>G16))),"No Unique Values")

error handling using IFERROR function

Things to Remember

  • Distinct values refer to the total unique values available in a dataset. While unique values of a dataset refer to the values that appear only once in the dataset.
  • While using the UNIQUE function, the results update automatically when the source data changes. Data change must be made within the cell reference.
  • If your dataset contains blank cells, empty strings, or zero values, the UNIQUE function will include them in the output.

Download Practice Workbook


In conclusion, this article aimed to provide a clear understanding of the concept of unique values in Excel. Understanding the significance of Excel unique values is essential for effective data analysis, reporting, and decision-making processes.

Whether you need to extract distinct values, remove duplicates, or filter data based on uniqueness, Excel offers various tools and functions to accomplish these tasks.

If you like this article, check out Exceldemy for more relevant content.

Frequently Asked Questions

Q1. How can I find unique values in Excel without removing duplicates?
One way to find unique values without removing duplicates is by using the Advanced Filter feature, which allows you to copy unique values to another location while keeping the original dataset intact.

Q2. Which Excel versions support the UNIQUE function?
The UNIQUE function is available in Excel 365 and later versions. It is not available in earlier versions of Excel, such as Excel 2016, Excel 2013, Excel 2010, or Excel 2007.
If you have an older version of Excel, you may need to use alternative methods like the “Remove Duplicates” feature or formulas to find unique values.

Q3. How do I create a unique value table in Excel?
First, open your Excel worksheet and select the range of data that you want to extract unique values from. Go to the Data tab > Data Tools group, > click the Remove Duplicates button.

A dialog box will appear showing a preview of the selected range with checkboxes next to each column. Click the “OK” button. Excel will remove any duplicate values from the selected range.

Now, copy the remaining unique values and paste them into a new location in your worksheet to create the unique value table

Q4. How do I use unique filters in Excel?
Excel does not have a built-in unique filter feature. However, you can use the advanced filter option.
First, select the dataset, then go to the Data tab > from the Sort & Filter group > select Advanced > from the new dialogue box, and select Unique records only.

Excel Unique Values: Knowledge Hub

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Ishrak Khan
Ishrak Khan

Qayem Ishrak Khan, BURP, Urban and Regional Planning, Chittagong University of Engineering and Technology, Bangladesh, has been working with the ExcelDemy project for 1 year. He wrote over 40+ articles for ExcelDemy. He is an Excel and VBA Content Developer providing authentic solutions to different Excel-related problems and writing amazing content articles regularly. Data Visualization, DBMS, and Data Analysis are his main areas of interest. Besides, He has passions about learning and working with different features of Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF