UNIQUE: make a list without repeated entries
UNIQUE is often the quickest way to make a compact list of customers, categories or departments. The real check is what should count as the same entry.
Formula syntax
=UNIQUE(array, [by_col], [exactly_once])
What goes into it
array
Range or array to deduplicate.
by_col
Optional TRUE to compare columns instead of rows.
exactly_once
Optional TRUE to return only values appearing exactly once.
A quick example first
=UNIQUE(A2:A100)
Returns the distinct values in A2:A100.
=SORT(UNIQUE(A2:A100))
Returns distinct values sorted alphabetically/numerically.
Separate a distinct list from values occurring once
Enter these customer names in A2:A6, preserving the repeats.
| Cell | Customer |
|---|---|
| A2 | Asha |
| A3 | Ben |
| A4 | Asha |
| A5 | Dev |
| A6 | Ben |
| Formula | Expected result / interpretation |
|---|---|
| =UNIQUE(A2:A6) | Asha, Ben, Dev — each distinct name appears once |
| =UNIQUE(A2:A6,FALSE,TRUE) | Dev — only this name occurs once in the source |
| =SORT(UNIQUE(A2:A6)) | Asha, Ben, Dev in ascending order |
The third argument answers a different question from ordinary deduplication. A customer who bought twice still belongs on a distinct customer list but disappears from the exactly-once list. Select the definition before reporting a customer count.
Before you copy this into a real sheet
This is Excel dynamic-array syntax. Clear the spill area and clean source labels before deduplicating. A trailing space can create a separate-looking value even when names appear identical on screen. Comparing multiple columns compares entire rows, not just the first column. If names are not reliable identifiers, deduplicate customer IDs instead; two different people can share a display name. A source change refreshes the list, so use a values-only copy for an intentionally frozen report.
Useful habits
- UNIQUE spills results into neighboring cells.
- Wrap with SORT when a predictable display order matters.
- Use exactly_once when you need values that occur only one time, not ordinary deduplication.
Where people get caught out
- Confusing “unique values” with “values occurring exactly once.”
- Placing the formula where the spill range is blocked.
Other functions worth a look
A detail worth remembering
Before publishing a distinct list, inspect capitalization, spaces and imported text. Values that look identical may not be stored identically.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.