How to Find Unique Values in Excel & Google Sheets

This tutorial demonstrates how to find unique values in Excel and Google Sheets.

 

 

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.

 

 

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.

 

 

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.

 

 

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

 

 

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)

 

 

The result is the same as when you use an advanced filter: Unique values are copied in Column C.

See also: Using Find and Replace in Excel VBA

Find Unique Values in Google Sheets

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