How to Use the UNIQUE Function in Excel
UNIQUE returns distinct values or rows from a range. Here's the syntax, common examples, and how to use it to build cleaner review views from messy spreadsheet data.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
UNIQUE returns distinct values or distinct rows from a range. Use it when you want a live de-duplicated list without manually removing duplicates from the source data.
That makes it useful for owner lists, category lists, project tags, client names, and any workflow where one messy table needs a cleaner derived view.
The syntax
=UNIQUE(array, [by_col], [exactly_once])- array — the range Excel should inspect
- [by_col] — optional; use
TRUEto compare columns instead of rows - [exactly_once] — optional; use
TRUEto return only values that appear once
Most of the time, you only need the first argument.
Basic example
If column B contains task owners and you want a distinct owner list:
=UNIQUE(B2:B100)Excel spills a clean list of each owner once.
That is a fast way to build review dropdowns or summary sections from a project tracker template.
Return unique rows instead of one column
If columns A:C contain task name, owner, and status:
=UNIQUE(A2:C100)Excel returns distinct rows across the full three-column range.
That is useful when you want to collapse repeated records into one live result set without changing the source table.
Return values that appear exactly once
If you only want values that occur one time:
=UNIQUE(B2:B100, FALSE, TRUE)That excludes repeated values and returns only entries that appear exactly once.
This is helpful when you need to identify one-off owners, categories, or tags instead of a general distinct list.
UNIQUE vs Remove Duplicates
| UNIQUE | Remove Duplicates | |
|---|---|---|
| Updates automatically | Yes | No |
| Changes the source data | No | Yes |
| Good for reusable views | Yes | No |
| Good for one-time cleanup | Sometimes | Yes |
If you need a live derived list, use UNIQUE.
If you want to permanently clean the source data one time, the Remove Duplicates tool may be better.
Common UNIQUE mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Source range has blanks you did not expect | Blank rows appear in the output | Filter or clean the source data first |
| Spill range is blocked | Excel returns #SPILL! | Clear the cells below or beside the formula |
| Expecting UNIQUE to sort the output | Results come back in source order | Wrap with SORT() if you want a sorted list |
| Using UNIQUE in older Excel versions | Function is unavailable | Use a modern Excel version with dynamic arrays |
✦ TIP
If you want a clean sorted owner list, combine the functions: =SORT(UNIQUE(B2:B100)).
Where UNIQUE fits best
UNIQUE is most useful when the workbook needs lightweight derived views such as:
- a distinct list of project owners
- a clean set of objective names in an OKR tracker
- a de-duplicated employee list in a schedule template
It is one of the easiest ways to make a messy operating sheet more reusable.
The Griddy way
UNIQUE is easy once you know the syntax, but people still waste time deciding which range to use, why the spill is blocked, or whether they should be de-duplicating rows or just one column.
"Pull a distinct list of project owners from this tracker, sort it alphabetically, and ignore any blank rows"
Griddy can build the formula chain and place the result into the summary view that needs it.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Build cleaner summary views from messy operating data
UNIQUE is a simple way to generate distinct owner, category, or tag lists from live planning and operations sheets without touching the source data.
Project Tracker
Track tasks, owners, priorities, due dates, and blockers in one delivery board. Group work by stream, review progress, and keep next steps visible.
Project Tracker for Marketing Teams
Track campaign work, owners, deadlines, approvals, and blockers in one free marketing project tracker spreadsheet for Excel and Google Sheets.
Project Tracker for Client Work
Track client deliverables, owners, due dates, approvals, and blockers in one free project tracker spreadsheet built for service teams.
OKR Tracker
Track company and team OKRs in one quarterly scorecard. Keep objective scores, KR progress, and leadership notes visible without needing dedicated OKR software.
OKR Tracker for Startups
Track startup objectives, key results, owners, progress, and confidence in one free OKR spreadsheet built for quarterly planning and review.
Employee Schedule
Plan a weekly staff rota with day-by-day shifts, weekly hours, overtime flags, and a manager snapshot block. Free template for teams, shops, and clinics.