Excel Tutorials
Get a live, auto-updating list of distinct values without deleting or touching a single row of your original data. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
UNIQUE is a dynamic array function that reads a range, figures out every distinct value in it, and spills that list of unique values into as many cells as it needs, starting from wherever you typed the formula. Nothing about the original range is touched, deleted, or reordered, UNIQUE just produces a fresh, separate, live list.
That "live" part is what sets it apart from Remove Duplicates: change the source data and the UNIQUE list updates itself automatically, where Remove Duplicates only runs once, as a one-time, permanent deletion.
Step-by-step, matching the video above.
Pick a cell with enough open space below and to the right, this is where the spilled list will appear.
=UNIQUE(A2:A100), pointing at the range you want distinct values from, then press Enter.
The result automatically fills downward into as many cells as there are unique values, no dragging or copying required.
Change a value in the original range and the UNIQUE list recalculates itself immediately, the same as any other live formula.
Wrap it in SORT: =SORT(UNIQUE(A2:A100)) returns the distinct values already in ascending order.
=COUNTA(UNIQUE(A2:A100)) or =ROWS(UNIQUE(A2:A100)) both return a single number: how many distinct values were found.
| A: Source (Vendor) | =UNIQUE(A2:A7) |
|---|---|
| Acme Supply | Acme Supply |
| Bolt & Co | Bolt & Co |
| Acme Supply | Party Rentals |
| Party Rentals | |
| Acme Supply | |
| Bolt & Co |
Six rows of source data with Acme Supply appearing three times and Bolt & Co twice, UNIQUE spills back exactly three distinct vendor names, in the order they first appeared, all from a single formula entered once in the top cell.
Beyond the basic range, UNIQUE takes two more optional arguments that change its behavior significantly.
=UNIQUE(A1:F2,TRUE) compares entire columns for uniqueness instead of rows, useful when your data runs sideways rather than down.
=UNIQUE(A2:A100,,TRUE) returns only values that appear exactly one single time, excluding anything that repeats even once. This is genuinely different from a plain distinct list, which would still include a value that appeared five times, just listed once.
=UNIQUE(range,TRUE,TRUE) applies exactly_once logic column-by-column instead of row-by-row, for the rare case both behaviors are needed together.
Both find distinct values, but they're built for genuinely different situations.
It creates a separate list elsewhere and updates automatically as source data changes, your original data is never touched.
It deletes duplicate rows directly from the data itself, a single operation that doesn't update automatically afterward.
UNIQUE requires Excel 365 or 2021+. Remove Duplicates works in every version, including much older ones, which can be the deciding factor for a file shared widely.
UNIQUE becomes considerably more useful nested inside, or wrapped around, other functions.
=UNIQUE(FILTER(A:A,B:B="Approved")) returns only the distinct values from column A where column B specifically says "Approved," skipping everything else entirely.
=SORT(UNIQUE(A2:A100)) is one of the most common combinations, a clean, alphabetized, distinct list in a single formula.
Since the result is a live range, it can be pointed at directly from Data Validation to build a drop-down that automatically includes new distinct values as they show up in the source data.
Excel 2019, 2016 and earlier don't have UNIQUE at all, and will show a #NAME? error instead. Confirm the Excel version before building a workbook around it.
If cells below or beside the formula already contain data, UNIQUE can't spill into them and returns a #SPILL! error instead of a result. Clear the blocking cells or start the formula somewhere with open space.
Only the top-left cell holds the actual formula, the rest of the spilled range is generated automatically and can't be edited individually. Edit the source formula instead.
With the third argument set to TRUE, UNIQUE excludes anything that repeats at all, even twice. It's a stricter filter than plain deduplication, not just a different way of saying the same thing.
A distinct list shouldn't need a formula to stay accurate.
Free 14-day trial. No credit card required.
UNIQUE returns a list of distinct values from a range, automatically spilling into as many cells as needed, without changing or deleting anything in the original data.
Excel 365 or Excel 2021 and later, along with Excel for the web. Older versions like Excel 2019 or 2016 don't support it and will show a #NAME? error.
UNIQUE creates a new, separate, live list of distinct values and leaves your original data untouched. Remove Duplicates permanently deletes duplicate rows from the data itself.
No. It reads from the range you give it and displays the result elsewhere, the source range is never modified.
It means something, usually existing data, is blocking the cells UNIQUE needs to spill its results into. Clear the blocking cells or move the formula somewhere with enough open space below and to the right.
Wrap it in SORT, like =SORT(UNIQUE(A2:A100)), to get the distinct values back in ascending order automatically.
Setting the third argument to TRUE, like UNIQUE(range,,TRUE), returns only values that appear exactly one single time in the range, excluding anything that repeats at all, even once.
Yes, if you select a multi-column range, UNIQUE compares entire rows and returns each distinct row combination rather than single values.
Wrap it in COUNTA or ROWS, like =COUNTA(UNIQUE(A2:A100)), to get a single number representing the total distinct count.
Yes, nesting FILTER inside UNIQUE, like =UNIQUE(FILTER(A:A,B:B="Approved")), returns only the distinct values from column A where column B says Approved.
Every business starts with a spreadsheet. Updoot is where you scale past it, records are unique and current automatically.
Start Your Free Trial →