Table of Contents
Unique Values
To find unique values in Excel use the Advanced Filter to extract unique values or filter for unique values. If you have Excel 365 or Excel 2021 use the magic UNIQUE function.
Extract Unique Values
When using the Advanced Filter in Excel always enter a text label at the top of each column of data.
1. Click a cell in the list range.
2. On the Data tab in the Sort & Filter group click Advanced.
The Advanced Filter dialog box appears.
3. Click Copy to another location (see image below).
4. Click in the Copy to box and select cell C1.
5. Check Unique records only.
6. Click OK.
Result:
Note: Excel removes all duplicate values (Lion in cell A7 and Elephant in cell A9) and sends the unique values to column C. You can also use this tool to extract unique rows in Excel.
Filter for Unique Values
Filtering for unique values in Excel is a piece of cake.
1. Click a cell in the list range.
2. On the Data tab in the Sort & Filter group click Advanced.
3. Click Filter the list in-place (see image below).
4. Check Unique records only.
5. Click OK.
Result:
Note: rows 7 and 9 are hidden. To clear this filter on the Data tab in the Sort & Filter group click Clear. You can also use this tool to filter for unique rows in Excel.
UNIQUE function
If you have Excel 365 or Excel 2021 simply use the magic UNIQUE function to extract unique values.
1. The UNIQUE function below (with no extra arguments) extracts unique values.
Note: this dynamic array function entered into cell C1 fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.
2. The UNIQUE function below extracts values that occur exactly once.
Note: the UNIQUE function has 2 optional arguments. The default value of 0 (second argument) tells the UNIQUE function to extract values from a vertical array. The value 1 (third argument) tells the UNIQUE function to extract values that occur exactly once.
Remove Duplicates
To find unique values (or unique rows) and delete duplicate values (or duplicate rows) at the same time use the Remove Duplicates tool in Excel.
On the Data tab in the Data Tools group click Remove Duplicates.
In the example below Excel removes all identical rows (blue) except for the first identical row found (yellow).
Note: visit our page about removing duplicates to learn more about this great Excel tool.
