UNIQUE Formula Generator
Describe the list you need deduplicated in plain English and get a ready-to-use UNIQUE formula for Excel or Google Sheets.
5 free tries without an account
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once])array is the range to deduplicate, by_col controls whether it compares rows or columns (leave as FALSE for a normal single-column list), and exactly_once — if set to TRUE — returns only values that appear a single time, instead of every distinct value. Formulon builds the right combination for whether you want "all distinct values" or "only the ones with no duplicates".
Examples
List unique customer names from a column with repeats
=UNIQUE(A2:A100)Spills a list of every distinct value found in A2:A100 into the cells below the formula, in the order they first appear, with no duplicates.
Count how many unique values are in a range
=COUNTA(UNIQUE(A2:A100))UNIQUE first produces the deduplicated list, then COUNTA counts how many entries are in that list — giving you the number of distinct values in A2:A100.
Frequently asked questions
What's the difference between the UNIQUE function and the Remove Duplicates tool?
Remove Duplicates permanently deletes duplicate rows from your data in place. UNIQUE is a formula — it spills a live, deduplicated list into new cells without touching or deleting anything in the original range, and updates automatically if the source data changes.
How do I count how many unique values are in a range?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)) counts how many distinct values UNIQUE spills out, which is the number of unique entries in the source range.
Is UNIQUE available in every version of Excel?
No — UNIQUE is a dynamic array function only available in Excel 365 and Excel 2021 or later. Older Excel versions need a different approach, such as COUNTIF-based helper formulas or the Remove Duplicates tool, since UNIQUE isn't supported there.
Does UNIQUE work in Google Sheets?
Yes, Google Sheets supports UNIQUE with the same syntax as Excel, and it's been available there for longer than in Excel.
Don't want to write it by hand?
Describe what you need in plain English and get a ready-to-use formula in seconds.
Try the formula generator