# How to list values that appear only once in Excel

Set UNIQUE's third argument, exactly_once, to TRUE: `=UNIQUE(B2:B12,FALSE,TRUE)` returns only the values with no repeats.

**Excel:** `=UNIQUE(B2:B12,FALSE,TRUE)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Order | Customer |  | One-time customers |
| SO-501 | Northwind |  |  |
| SO-502 | Contoso |  |  |
| SO-503 | Northwind |  |  |
| SO-504 | Fabrikam |  |  |
| SO-505 | Litware |  |  |
| SO-506 | Contoso |  |  |
| SO-507 | Adatum |  |  |
| SO-508 | Fabrikam |  |  |
| SO-509 | Wingtip |  |  |
| SO-510 | Northwind |  |  |
| SO-511 | Tailspin |  |  |

Formula in D2: `=UNIQUE(B2:B12,FALSE,TRUE)`

Result (spills 4 × 1): Litware; Adatum; Wingtip; Tailspin

## 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.

## Other ways

- `=FILTER(B2:B12,COUNTIF(B2:B12,B2:B12)=1)`: The same list built with COUNTIF. Change =1 to =2 to find values that appear exactly twice.
- `=UNIQUE(B2:B12)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/list-values-that-appear-only-once