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.

Excel UNIQUE function returning a sorted distinct list without blanks
← Tutorial HubFormulas & Functions

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

  1. Confirm the source column contains the intended field.
  2. Exclude blanks when an empty list item would be confusing.
  3. Apply UNIQUE to return distinct values.
  4. Wrap the result in SORT when a consistent order is useful.
  5. Use the spill reference, such as G2#, when another formula or chart needs the full result.
Excel UNIQUE setup showing source column, spill range, blank handling, and optional SORT

Common formula mistakes

MistakeWhy it matters
Using Remove Duplicates on the sourceThat changes the original data instead of creating a reporting list.
Leaving inconsistent spellingEast and East with a trailing space become separate results.
Blocking the spill rangeExisting content prevents the list from expanding.
Using a fixed range that omits new rowsAn Excel Table or appropriate dynamic source is safer.
Expecting availability in older ExcelUNIQUE requires dynamic-array support.
Excel UNIQUE worked example returning distinct values, excluding blanks, and sorting the spill result

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.

Excel UNIQUE verification checking duplicates, blanks, sort order, spill errors, and source updates

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.