English

List Unique Values in Excel and Google Sheets

UNIQUE spilled four SKUs plus a blank; sku-103 stayed distinct from SKU-103. COUNTUNIQUE was 4; UNIQUE with exactly-once returned SKU-110 and sku-103.

Excel How-to By Oscar 3 min read Published
On this page

Which versions this applies to

The results below come from Google Sheets on November 12, 2026. Microsoft documents UNIQUE for Excel 365 / Excel 2021. It is not in Excel 2016 or 2019. COUNTUNIQUE is a Google Sheets function; I am not quoting an Excel equivalent from this test. I did not re-run this list in desktop Excel today.

The list I used

A2:A8 held: SKU-103, SKU-101, SKU-103, SKU-110, sku-103, a blank cell, and SKU-101 again.

=UNIQUE(A2:A8)
=UNIQUE(A2:A8,FALSE,TRUE)
=COUNTUNIQUE(A2:A8)
=COUNTA(UNIQUE(A2:A8))

UNIQUE spilled SKU-103, SKU-101, SKU-110, sku-103, then a blank cell. The first occurrence of each value is the one that is kept, in that order. sku-103 was not merged into SKU-103. That is the opposite of COUNTIF and XLOOKUP, which ignored case in Case-Sensitive Lookup.

The third argument TRUE means “exactly once”: UNIQUE(A2:A8,FALSE,TRUE) spilled only SKU-110 and sku-103. SKU-103 and SKU-101 appear twice in the source, so they dropped out. The second argument FALSE means “by row,” which is the default I would pass explicitly if I also need the third argument.

COUNTUNIQUE and a blank cell

COUNTUNIQUE(A2:A8) returned 4. COUNTA(UNIQUE(A2:A8)) returned 5. The unique spill includes the blank as an extra item; COUNTUNIQUE did not count that blank, and COUNTA did, because a zero-length entry is not the same as an omitted cell for COUNTA. If you want a count of non-blank distinct codes, COUNTUNIQUE matched the four SKU strings in this file. If you need to know that a blank made it into the unique list, look at the spill, or compare the two counts.

Excel and Google Sheets differences

Current Excel has UNIQUE with the same by-column / exactly-once arguments, according to Microsoft. I did not re-test those arguments in Excel today. COUNTUNIQUE is the Sheets function I actually ran; I would not invent an Excel synonym for it here. Highlighting duplicates, rather than listing the unique set, is Highlight Duplicates Across Two Columns.

Common mistakes

  • Expecting UNIQUE to treat SKU-103 and sku-103 as one value. It kept both.
  • Comparing COUNTUNIQUE with COUNTA(UNIQUE(...)) and assuming they must match. They were 4 and 5 because of the blank.
  • Using UNIQUE(...,TRUE) and thinking the first argument flip means “exactly once.” The “exactly once” switch is the third argument.
  • Leaving values in the spill area. UNIQUE needs empty cells below the formula, the same way SPLIT needs empty cells to the right.

FAQ

How do I list unique values in Google Sheets?

=UNIQUE(A2:A8) spilled the distinct SKUs in first-seen order, and kept sku-103 separate from SKU-103.

How do I list values that appear only once?

=UNIQUE(A2:A8,FALSE,TRUE) spilled SKU-110 and sku-103 from this list.

Why is COUNTUNIQUE different from COUNTA of UNIQUE?

The unique spill included a blank from the source range. COUNTUNIQUE was 4; COUNTA(UNIQUE(A2:A8)) was 5.

Is UNIQUE case-sensitive?

In this file, yes: SKU-103 and sku-103 both appeared in the spill.

Will UNIQUE work in Excel 2016?

Microsoft does not list UNIQUE for Excel 2016 or 2019. I did not build an older-Excel workaround as part of this test.

Live-test findings

Google Sheets, November 12, 2026. Source: SKU-103, SKU-101, SKU-103, SKU-110, sku-103, blank, SKU-101. UNIQUE spilled SKU-103, SKU-101, SKU-110, sku-103, and a blank. UNIQUE(...,FALSE,TRUE) spilled SKU-110, sku-103. COUNTUNIQUE was 4. COUNTA(UNIQUE(...)) was 5.

Sources

Oscar

Spreadsheet formulas and troubleshooting. See each article for its actual verification environment and pending author checks.

Tested in Google Sheets, November 12, 2026. Excel version notes follow Microsoft documentation and were not re-tested in a desktop Excel workbook on this date..

#unique#filter#duplicates#countunique
Next in Data Cleaning & Entry Extract Text Between Two Characters in Excel and Google Sheets