When a column contains repeated customers, products, departments or other values, you may need a clean list of the distinct entries. The UNIQUE function can generate that list without manually deleting duplicates from the source data.
Basic UNIQUE syntax
=UNIQUE(array,[by_col],[exactly_once])With the default settings, UNIQUE compares rows and returns each distinct value or row once. The optional exactly_once argument changes the result so that only values appearing exactly one time are returned.
Example: create a unique customer list
=UNIQUE(B2:B100)The result spills into the cells below the formula and updates as the source data changes, subject to how the source range is defined.
Sort the unique list
=SORT(UNIQUE(B2:B100))This is useful for creating an alphabetical list of customers, departments or categories.
Find values that occur exactly once
=UNIQUE(B2:B100,,TRUE)This is different from a normal distinct list. A normal UNIQUE result keeps one copy of repeated values; exactly_once returns only values that occur one time in the source.
Compare two columns
UNIQUE can also be part of a comparison workflow. For example, you can combine or filter values from two lists to identify entries that need review. For simple one-column comparisons, start by deciding whether you need distinct values, values missing from one list, or values that occur exactly once.
Common mistakes
- Assuming UNIQUE removes duplicates from the original data. It does not; it returns a separate result.
- Using a fixed source range when the dataset regularly grows.
- Ignoring spaces or inconsistent text that make visually similar values different.
- Forgetting that dynamic-array results need room to spill.
UNIQUE versus Remove Duplicates
Use UNIQUE when you want a formula-driven list while preserving the original records. Use Remove Duplicates when you intentionally want to change the selected dataset. If the original data matters, review or copy it before using a destructive cleanup operation.
Compatibility note
UNIQUE is a modern dynamic-array function. Check the Excel version used by your audience before relying on it in a shared workbook.
Practical checklist
- Decide whether you need distinct values or values occurring exactly once.
- Use SORT when the result should be ordered.
- Use an Excel Table or structured references for expanding datasets.
- Check for hidden spaces or inconsistent labels before treating values as different.
- Keep the source data intact when the unique list is for reporting or validation.
Migration 074: UNIQUE refinement.
Distinct values versus values that occur once
This is the distinction that causes the most confusion with UNIQUE. =UNIQUE(B2:B100) returns one copy of each distinct value. It does not mean that every returned value appeared only once. If you need only values that occur exactly one time, use the third argument:
=UNIQUE(B2:B100,,TRUE)Use the first formula for a clean list of departments or customers. Use exactly_once=TRUE when the question is specifically, “Which values appear only once?”
UNIQUE does not clean the source column
The result is a separate dynamic list. It does not delete or alter the repeated records in the source range. That makes UNIQUE a safer choice when the original dataset must remain intact for audit, reporting, or later analysis.
UNIQUE and SORT together
If the user needs a distinct list in a predictable order, combine the functions:
=SORT(UNIQUE(B2:B100))This is useful for building a clean list of customers, departments, product codes, or validation choices from a larger dataset.
Why a UNIQUE result may look wrong
Excel compares the actual cell values. Extra spaces, inconsistent spellings, or values stored in different forms can make entries that look identical on screen appear as different values. Clean the source data before treating the UNIQUE result as a data-quality report.
Spill and version considerations
UNIQUE returns a dynamic array, so the cells below or beside the formula must be available for the result. A blocked spill range can produce #SPILL!. Microsoft currently lists UNIQUE for Microsoft 365 and Excel 2021 and later releases, so check compatibility when the workbook will be shared with users on older versions.