=UNIQUE(array, [by_col], [exactly_once]) UNIQUE extracts all distinct values from a column or range, automatically discarding duplicates. To sort the distinct list alphabetically, wrap it in SORT: =SORT(UNIQUE(A2:A100)).
UNIQUE Function in Excel & Google Sheets
The UNIQUE function extracts distinct items from a dataset, replacing the legacy manual “Remove Duplicates” tool with a live, dynamic formula.
1. Extracting a Distinct List
To extract a clean list of unique customer names from a transaction log:
=UNIQUE(A2:A1000)2. Sorting Unique Values Alphabetically
Combine SORT with UNIQUE for dropdown lists and reporting summaries:
=SORT(UNIQUE(A2:A1000))3. Finding Items That Occur Exactly Once
To identify customers who made only a single purchase (no repeat orders), set the 3rd argument to TRUE:
=UNIQUE(A2:A1000, FALSE, TRUE)Frequently Asked Questions
What is the difference between distinct values and values appearing exactly once?
Distinct values include every unique item once, even if it was duplicated in the original dataset. 'Exactly once' (3rd argument TRUE) discards any item that appeared more than once, returning only pure singletons.
Can UNIQUE extract unique rows across multiple columns?
Yes! If you pass a multi-column range (e.g. A2:B100), UNIQUE returns unique combinations of Column A and Column B.