Excel Unique Values: list each item once

A column of cities with repeats, and you want each city once. Three ways, newest first.

Way 1: UNIQUE (Excel 2021 / 365)

1. Click an empty cell, say C1.
2. Type =UNIQUE(A1:A100). Enter.

The list spills down automatically and updates when column A changes. Sorted too: =SORT(UNIQUE(A1:A100)).

Way 2: Remove Duplicates (any Excel, one-off)

1. Copy column A to a new column.
2. Click in the copy › Data › Remove Duplicates › OK.

Full steps: remove duplicates.

Way 3: Advanced Filter (older Excel, keeps original)

1. Click in column A › Data › Advanced.
2. Choose Copy to another location. Copy to: click C1.
3. Tick Unique records only. OK.

Count the unique values

Newer Excel: =COUNTA(UNIQUE(A1:A100)). Older:

=SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100))

(No blanks allowed in the range for the older formula.)

Values that appear only once

=UNIQUE(A1:A100, FALSE, TRUE)

The third TRUE means “exactly once” — items that repeat are left out entirely.

Check it worked

Count the result list. It must be smaller than or equal to the original, and no city appears twice.

Common mistake

“Pune” and “Pune ” (trailing space) count as two. TRIM the column first.