How to list values that appear only once in Excel
Formula (Excel and Google Sheets)
=UNIQUE(B2:B12,FALSE,TRUE)Set UNIQUE's third argument, exactly_once, to TRUE: =UNIQUE(B2:B12,FALSE,TRUE) returns only the values with no repeats.
- Result on the example
- a 4-row list that spills from D2 in
D2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE's second argument chooses rows or columns (FALSE means compare rows), and the third, exactly_once, drops every value that appears more than once instead of keeping one copy. What's left are the customers who ordered a single time.
=UNIQUE(B2:B12,FALSE,TRUE)Result{4 rows × 1 col} "Litware", "Adatum", "Wingtip", "Tailspin"
B2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …A range: 11 cells in one column.
UNIQUE(B2:B12,FALSE,TRUE){4 rows × 1 col} "Litware", "Adatum", "Wingtip", "Tailspin"UNIQUE: Distinct rows of a range. Spills.- array
B2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", … - by_col
FALSE - exactly_once
TRUE
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(B2:B12,FALSE,TRUE). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(B2:B12,COUNTIF(B2:B12,B2:B12)=1) | spills 4 × 1 | The same list built with COUNTIF. Change =1 to =2 to find values that appear exactly twice. |
| =UNIQUE(B2:B12) | spills 7 × 1Different result | Without exactly_once, every customer is listed once, repeat buyers included. All seven customers. |
Common mistakes
- Leaving the second argument empty works in Excel (
UNIQUE(B2:B12,,TRUE)), but writing FALSE makes the formula clear and works the same in Google Sheets. - A trailing space makes "Contoso " a different customer from "Contoso", so both show up as one-time customers.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
List values that appear only once
D2, list the customers in B2:B12 who placed exactly one order.Questions
How do I count the customers who ordered once?
Count the list: =ROWS(UNIQUE(B2:B12,FALSE,TRUE)).
How do I list the repeat customers instead?
Keep counts above 1 and remove the repeats: =UNIQUE(FILTER(B2:B12,COUNTIF(B2:B12,B2:B12)>1)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.