列出唯一值：Excel 與 Google Sheets

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.

Related tutorials

• Compare Two Columns in Excel and Google Sheets and Find Missing Values
• Case-Sensitive Lookup in Excel and Google Sheets
• Highlight Duplicates Across Two Columns in Google Sheets (Conditional Formatting)

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

• Microsoft Support: UNIQUE function: https://support.microsoft.com/en-us/excel/functions/unique-function
• Google Docs Editors Help: UNIQUE: https://support.google.com/docs/answer/10522653
• Google Docs Editors Help: COUNTUNIQUE: https://support.google.com/docs/answer/3093487