See all How-To Articles

How to Find Unique Values in Excel & Google Sheets

In this tutorial, you will learn how to find unique values in Excel and Google Sheets.

 

find unique values final data

 

Find Unique Values Using Advanced Filter

The first option to find and extract unique values in Excel is to use an advanced filter. Say you have the list of names, shown below, with duplicates.

 

find unique values initial data

 

To get a list of unique values (without duplicates) in Column C, follow these steps:

1. Select any cell in the data range (B2, for example) and in the Ribbon, go to Data > Advanced.

 

find unique values advanced filter

 

2. In the Advanced Filter window, (1) select Copy to another location. (2) In the Copy to box, enter the cell where you want the copied list of unique values to start (e.g., C1). Then (3) check Unique records only and (4) click OK.

Note that, while you can also filter a data range in-place by selecting the first Action, that would be practically the same as filtering duplicate values.

 

find unique values advanced filter 2

 

As a result, in Column C you get all unique values from Column B, without duplicates.

 

find unique values final data

 

You can also use VBA code to find unique values in Excel.

Find Unique Values Using the UNIQUE Function

You can also use the UNIQUE Function to achieve the same thing. This function extracts a list of unique values from a given range. To this, in cell C2, enter the formula:

=UNIQUE(B2:B13)

 

find unique values unique function

 

The result is the same as when you use Advanced Filter: Unique values are copied in Column C.

Find Unique Values in Google Sheets

There is no Advanced Filter option in Google Sheets, but you can use the UNIQUE Function the same way in both Excel and Google Sheets.