UNIQUE returns distinct values or rows without changing the source range. The result keeps the order in which each value or row first appears.
For a list in A2:A7, enter =UNIQUE(A2:A7) in an empty cell. The result expands down automatically.
Extract Unique Values From a Column
Enter this tested list in A1:A7:
| Fruit |
|---|
| Apple |
| Banana |
| Apple |
| Pear |
| Banana |
| Plum |
=UNIQUE(A2:A7)The formula returned Apple, Banana, Pear, and Plum. Each value appeared once, in first-seen order.
The formula does not delete duplicates from A2:A7. It creates a separate result that updates when the source list changes.
UNIQUE Function Syntax
=UNIQUE(range,[by_column],[exactly_once])- range contains the values, rows, or columns to compare.
- by_column is FALSE by default, so Sheets compares rows.
- exactly_once is FALSE by default, so repeated entries are returned once.
Set by_column to TRUE when repeated records run across columns. Set exactly_once to TRUE when repeated entries should be excluded completely.
Return Unique Rows From a Table
When the range has several columns, UNIQUE compares the complete row. Two rows are duplicates only when every included cell matches.
In a separate table, place the following records in M1:O5:
| Name | Department | Region |
|---|---|---|
| Mina | Sales | West |
| Mina | Sales | West |
| Omar | Support | East |
| Mina | Sales | East |
=UNIQUE(M2:O5)If two employees share a department but have different names, both rows remain because the complete A:C records differ.
Return Only Values That Appear Once
Set the third argument to TRUE to remove every value that has a duplicate:
=UNIQUE(A2:A7,FALSE,TRUE)With the tested fruit list, the result was Pear and Plum. Apple and Banana were excluded because each appeared twice.
Compare and Return Unique Columns
Set the second argument to TRUE when records run horizontally:
=UNIQUE(K2:N3,TRUE,FALSE)In the test, the Red/1 column appeared twice. The formula returned Red/1, Blue/2, and Green/3 once each.
Remove Blanks From a UNIQUE Result
UNIQUE treats blank as a value. Repeated blank source cells produce one blank row in the result.
The test returned North, “North ” with a trailing space, north, a blank row, and East. Each distinct text value remained separate.
Use FILTER when blank output rows are unwanted:
=UNIQUE(FILTER(A2:A,A2:A<>""))Sort or Count the Result
Wrap UNIQUE in SORT for alphabetical output:
=SORT(UNIQUE(A2:A7))For a direct count, COUNTUNIQUE is usually clearer. Filter blanks first when blank should not count.
The output needs empty cells. In the test, content in C11 blocked a UNIQUE formula in C10 and produced #REF!.
See Google’s UNIQUE reference for the optional arguments and official examples.
Other Google Sheets articles you may also like