UNIQUE - Return distinct entries or entries seen once
Build a department directory and distinguish removing repeated labels from selecting labels that occur exactly once.
By the end, you can
- Build a distinct-label list.
- Choose whole-row or column comparison.
- Distinguish distinct values from frequency-one values.
Before you start: Understand source rows, spilled arrays, and COUNTIF/COUNTA reporting questions.
In this lesson
Repeated values can still belong in a distinct list
UNIQUE returns distinct rows or columns from an array. HR can list departments without repeating Sales for every employee. Its default includes Sales once even if it occurs many times. The exactly_once option asks a different question: which source entries occurred only once at all?
Syntax and arguments
=UNIQUE(array, [by_col], [exactly_once])Defaults compare rows and return all distinct entries, not just entries with frequency one.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | Values or records to compare. | B2:B7 |
by_colOptional | FALSE compares rows; TRUE compares columns. | FALSE |
exactly_onceOptional | TRUE keeps only entries occurring once; FALSE/default keeps every distinct entry. | TRUE |
An HR department directory
Sales| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | Directory | |
| 2 | Anika | Sales | Sales | |
| 3 | Dev | Finance | Finance | |
| 4 | Meera | Sales | Operations | |
| 5 | Rahul | Operations | HR | |
| 6 | Zoya | HR | ||
| 7 | Farah | Finance |
Sort, count, or request frequency one
=SORT(UNIQUE(B2:B7))UNIQUE deduplicates; SORT explicitly orders the result.
=UNIQUE(B2:B7,FALSE,TRUE)Operations and HR occur once each. Sales and Finance are excluded because they recur.
=COUNTA(UNIQUE(B2:B7))Count the four populated entries in the distinct vector, not the six employee records.
Rows, blanks, and spill rules
- Comparing a multi-column array compares whole records. Employee+department rows remain distinct when employees differ, even if departments repeat.
- Whitespace differences can leave separate-looking labels. UNIQUE is not a cleanup function; normalize only according to the data policy.
- Unused source cells can introduce blank-looking or zero entries. Do not feed a large empty tail to a directory without considering its meaning.
- The result spills and a blocked destination gives #SPILL!. Keep the anchor outside an Excel Table; an expanding source can use structured references.
- UNIQUE is available in Microsoft 365 and Excel 2021/2024. Its optional settings are separate from the default distinct-row operation.
Common mistakes and Practice scope
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.