FORMULAS & FUNCTIONS
How to Return a Unique List with the UNIQUE Function
Create a dynamic list of unique values in Excel, remove blanks, and keep the result updated without using Remove Duplicates.
UNIQUE returns one copy of each distinct value from a range. The result is a dynamic spilled list, which means Excel fills the required output cells automatically and updates the list when the source changes. Keep the spill area empty and decide whether blanks should appear before building the final formula.
Build the UNIQUE dynamic list formula
Assume source categories are in A2:A500.
=SORT(UNIQUE(FILTER(A2:A500,A2:A500<>""))) A second useful version is:
=UNIQUE(tblData[Category]) FILTER removes blank source values, UNIQUE returns one instance of each remaining value, and SORT presents the list in a stable order.
If you need to sort full rows by a related value, continue with the SORTBY formula workflow.
Step-by-step formula workflow
- Confirm the source column contains the intended field.
- Exclude blanks when an empty list item would be confusing.
- Apply UNIQUE to return distinct values.
- Wrap the result in SORT when a consistent order is useful.
- Use the spill reference, such as G2#, when another formula or chart needs the full result.
Common formula mistakes
| Mistake | Why it matters |
|---|---|
| Using Remove Duplicates on the source | That changes the original data instead of creating a reporting list. |
| Leaving inconsistent spelling | East and East with a trailing space become separate results. |
| Blocking the spill range | Existing content prevents the list from expanding. |
| Using a fixed range that omits new rows | An Excel Table or appropriate dynamic source is safer. |
| Expecting availability in older Excel | UNIQUE requires dynamic-array support. |
Clean the source before asking UNIQUE to deduplicate it
UNIQUE distinguishes values by their stored content. East, East , and a visually identical value containing a nonbreaking space can appear as separate items. If the list feeds a report or validation control, normalize the source with TRIM, CLEAN, or an approved helper formula first instead of blaming UNIQUE for inconsistent results.
If duplicates are a broader row-level problem, use the remove duplicate rows safely guide before building the final list.
A spilled result can also become the source for another formula. If the unique list begins in G2, G2# refers to the entire current spill range and expands or contracts automatically. That is useful for summaries and charts. For Data Validation, compatibility varies by Excel version and setup, so test the intended reference method in the target workbook rather than assuming every version accepts a direct spill reference the same way.
Use the unique list as a controlled dimension
A spilled UNIQUE result can become the approved list of categories used by a report or summary, but only after the source has been cleaned. If the source contains both “Marketing” and “Marketing ”, the result exposes the inconsistency rather than fixing it. Treat that as a data-quality signal. Once normalized, the unique list is useful for selector controls, category audits, and checking whether unexpected new values appeared.
Frequently asked questions
How do I exclude blanks?
Filter the source for nonblank values before applying UNIQUE.
Can I count each unique item?
Use COUNTIF or COUNTIFS against the spilled unique list.
How do I reference the whole result?
Use the spill operator, for example G2#.
Why do apparently identical values appear twice?
Check extra spaces, nonprinting characters, and inconsistent data types.
Continue building reliable Excel workflows
Browse more lessons in the OneXcel Tutorial Hub or explore free Excel resources.
