Cellwise Try it free

Excel guide · 3 min read

How to create a unique list in Excel

Quick answerType =UNIQUE(B2:B9) in an empty cell to list every value from B2:B9 once, in the order it first appears. Wrap it as =SORT(UNIQUE(B2:B9)) for an A to Z list. The list spills down and updates when the data changes. UNIQUE is in Excel 365 and Excel 2021 and later; older versions use Advanced Filter.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1TicketCustomerCategoryAgent
2T-901NorthwindBillingRosa
3T-902ContosoLoginDev
4T-903NorthwindLoginRosa
5T-904FabrikamBillingKai
6T-905BugDev
7T-906ContosoBillingKai
8T-907AdatumBugRosa
9T-908NorthwindLoginRosa

Download this sheet as an Excel practice file (.xlsx)

UNIQUE: one copy of each value

This support log has eight tickets from four customers. UNIQUE keeps the first time each customer appears and drops the repeats. Your source data is not touched.

Ticket T-905 has no customer, so the list includes an empty entry for it, which shows as 0 in Excel. The next section removes it.

=UNIQUE(B2:B9)

Result: spills 5 rows × 1 column

Northwind
Contoso
Fabrikam
0
Adatum

Spills 5 rows: Northwind, Contoso, Fabrikam, an empty entry for T-905, and Adatum.

Skip blanks, then sort

Filter out the empty cells before UNIQUE sees them, then sort the result. This is the version to use for a dropdown or a report header, because a stray blank or 0 at the end of a list looks like a mistake.

=SORT(UNIQUE(FILTER(B2:B9,B2:B9<>"")))

Result: spills 4 rows × 1 column

Adatum
Contoso
Fabrikam
Northwind

4 customers, A to Z: Adatum, Contoso, Fabrikam, Northwind.

Unique values for one category

Filter by a condition first, then take the unique values. Here: which customers raised Login tickets. Northwind had two Login tickets but appears once.

=UNIQUE(FILTER(B2:B9,C2:C9="Login"))

Result: spills 2 rows × 1 column

Contoso
Northwind

2 customers: Contoso and Northwind.

Unique combinations of two columns

Give UNIQUE two or more adjacent columns and it compares whole rows. You get each distinct pair once. This answers questions like "which agent has handled which category".

=UNIQUE(C2:D9)

Result: spills 6 rows × 2 columns

BillingRosa
LoginDev
LoginRosa
BillingKai
BugDev
BugRosa

Spills 6 rows x 2 columns: Billing/Rosa, Login/Dev, Login/Rosa, Billing/Kai, Bug/Dev and Bug/Rosa. Login/Rosa and Billing/Kai each happened twice but are listed once.

Values that appear exactly once, and counting the list

UNIQUE has a third argument, exactly_once. =UNIQUE(B2:B9,,TRUE) returns only the values that occur once: Fabrikam and Adatum here, plus the empty entry for T-905 unless you filter blanks out first, which is different from removing duplicates. That form works in Excel 365, but the Cellwise practice engine does not run it.

To count how many distinct values the list has, wrap it in COUNTA. The count-unique-values guide covers this in depth.

=COUNTA(UNIQUE(FILTER(B2:B9,B2:B9<>"")))

Result: 4

4 distinct customers.

Older Excel: Advanced Filter, and using the list in a dropdown

Advanced Filter copies distinct values once; it does not update. In Excel 365, point a dropdown at the spilled list with the # sign so new values appear automatically.

  1. Older Excel: select B1:B9 including the header, go to Data > Advanced.
  2. Choose Copy to another location, set Copy to (for example F1), tick Unique records only, click OK.
  3. Excel 365 dropdown: put =SORT(UNIQUE(FILTER(B2:B9,B2:B9<>""))) in F2.
  4. Select the input cell, go to Data > Data Validation > List, and enter =F2# as the Source.

Common mistakes

Values that look the same are listed twice

A trailing space or a different spelling makes them different values. UNIQUE ignores case, so "northwind" and "Northwind" merge, but "Northwind " does not. Clean with TRIM first: =UNIQUE(TRIM(B2:B9)).

A 0 at the end of the list

An empty cell in the source shows up as 0. Filter blanks out with FILTER(B2:B9,B2:B9<>"") inside UNIQUE.

#SPILL! error

The cells the list needs are not empty, or the formula sits inside an Excel table. Move it to a clear area of the sheet.

Expecting UNIQUE to delete duplicates from the data

UNIQUE builds a new list and leaves the source alone. To delete repeated rows in place, use Data > Remove Duplicates on a copy.

FAQ

What is the difference between unique and distinct in Excel?

People use both words for "each value once", which is what UNIQUE returns by default. Strictly, unique can also mean values that occur only once; UNIQUE with TRUE as its third argument returns those.

Is UNIQUE case-sensitive?

No. UNIQUE treats "Contoso" and "CONTOSO" as the same value and keeps the first one it sees.

How do I make a unique list across two columns?

If the columns are side by side, UNIQUE(C2:D9) returns unique pairs. To merge two separate lists into one unique list in Excel 365, stack them with VSTACK first: =UNIQUE(VSTACK(B2:B9,F2:F9)).

How do I create a unique list without UNIQUE?

Use Data > Advanced with Unique records only, or copy the column and run Data > Remove Duplicates on the copy. A PivotTable with the column in Rows also lists each value once.

Functions used

Related guides

Updated