How to Find Unique Values in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on November 21, 2022

This tutorial demonstrates 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


  1. 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:



find unique values unique function


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.

unique values gs

AI Formula Generator

Try for Free

See all How-To Articles