In this tutorial, you will learn 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:
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.