UNIQUE returns each distinct value of a range once, and an empty cell counts as a value. So a list with gaps, or a range that runs to the bottom of the sheet, gives you a blank line in the result. The usual fix is to filter the blanks out before UNIQUE sees them:
=UNIQUE(FILTER(A2:A, A2:A<>""))
That works, but “blank” can mean three different things in a spreadsheet, and our tests showed each fix treats them differently.
Tested in Google Sheets. On November 11, 2026 we ran each formula on this page on the same nine cells and recorded every value it returned. How we test
The test list
Nine cells: Apple, an empty cell, Banana, Apple, another empty cell, Cherry, a cell with a single space, apple in lower case, and a formula that returns an empty text, ="".
Plain =UNIQUE(K1:K9) returned 7 rows: Apple, a blank, Banana, Cherry, the space, apple and a second blank. The truly empty cells were merged into one blank, but the empty text from ="" came out as a separate entry.
Which formula removes what
| Formula | Rows | Empty cells | ="" |
A space |
|---|---|---|---|---|
=UNIQUE(K1:K9) |
7 | Kept (once) | Kept | Kept |
=UNIQUE(FILTER(K1:K9, K1:K9<>"")) |
5 | Removed | Removed | Kept |
=UNIQUE(FILTER(K1:K9, LEN(K1:K9))) |
5 | Removed | Removed | Kept |
=UNIQUE(TOCOL(K1:K9, 1)) |
6 | Removed | Kept | Kept |
=UNIQUE(FILTER(K1:K9, TRIM(K1:K9)<>"")) |
4 | Removed | Removed | Removed |
Found in testing: TOCOL(range, 1), often suggested for skipping blanks, ignored the truly empty cells but not the empty text returned by ="". If your column is filled by formulas such as =IF(B2="", "", B2), those “empty” results are text, and TOCOL keeps them. The FILTER version removed both.
The recommended formula
For most lists, this removes empty cells, empty text and cells that only hold spaces:
=UNIQUE(FILTER(A2:A, TRIM(A2:A)<>""))
On our list it returned Apple, Banana, Cherry and apple, both with the range K1:K9 and with the whole column K:K. Note that it filters out space-only cells but doesn’t trim the values it keeps. If some of your entries have stray spaces, such as “Apple ” with a trailing space, they will still count as different. Clean them first with our guide to removing extra spaces.
Sorted
Wrap it in SORT: =SORT(UNIQUE(FILTER(K1:K9, K1:K9<>""))) returned the space first, then Apple, apple, Banana, Cherry. A space sorts before letters, which is another reason to use the TRIM version. Add 1, FALSE to sort from Z to A: =SORT(UNIQUE(FILTER(K1:K9, K1:K9<>"")), 1, FALSE) returned Cherry, Banana, Apple, apple and the space.
Apple and apple are different
UNIQUE is case-sensitive: “Apple” and “apple” both appeared in every result. That is different from conditional formatting, where we found that ="Late" matched “LATE”. To merge them, convert everything to lower case inside the formula: =UNIQUE(FILTER(LOWER(K1:K9), TRIM(K1:K9)<>"")) returned apple, banana and cherry.
Counting unique values
=COUNTUNIQUE(K1:K9)returned 5: it skipped the empty cells and the empty text, but counted the space and “apple” separately.=COUNTA(UNIQUE(K1:K9))returned 6, because COUNTA counts the empty text as a value.=ROWS(UNIQUE(FILTER(K1:K9, K1:K9<>"")))returned 5, the length of the filtered list.
Values that appear exactly once
UNIQUE has an optional third argument. =UNIQUE(K1:K9, FALSE, TRUE) returns only values that occur a single time. It dropped Apple (which appears twice) and the two empty cells, but kept Banana, Cherry, the space, apple and the empty text. Filter first if you need it clean.
Unique values with a count: QUERY
QUERY can list each value with how many times it appears and skip blanks in one go:
=QUERY(K1:K9, "select K, count(K) where K is not null group by K", 0)
It returned the space (1), Apple (2), Banana (1), Cherry (1) and apple (1). is not null left out both the empty cells and the empty text, but not the space. Use 1 instead of 0 as the last argument if the range starts with a header. More QUERY examples are in our QUERY ORDER BY guide and the QUERY formula generator.
Several columns
UNIQUE also works on whole rows. Filter on the column that must not be empty: =UNIQUE(FILTER(A2:B8, A2:A8<>"")) returned each region and rep pair once.