Why does UNIQUE return 0 for blank cells in Excel
Formula (Excel and Google Sheets)
=UNIQUE(FILTER(A2:A15,A2:A15<>""))UNIQUE treats an empty cell as a value and shows it as 0. Remove the blanks first: =UNIQUE(FILTER(A2:A15,A2:A15<>"")).
- Result on the example
- a 5-row list that spills from C2 in
C2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
How it works
An empty cell is one of the "values" in the range, so UNIQUE keeps one of it, and a reference to an empty cell displays as 0. FILTER with A2:A15<>"" removes the empty cells before UNIQUE sees them. The range can then run well past the data to leave room for new rows.
=UNIQUE(FILTER(A2:A15,A2:A15<>""))Result{5 rows × 1 col} "Finance", "Sales", "IT", "Marketing", …
A2:A1514 rows × 1 col: "Finance", "Sales", (empty), "IT", …A range: 14 cells in one column.
A2:A15<>""{14 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The <> operator tests whether they differ.
FILTER(A2:A15,A2:A15<>""){8 rows × 1 col} "Finance", "Sales", "IT", "Sales", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A1514 rows × 1 col: "Finance", "Sales", (empty), "IT", … - condition1
A2:A15<>""{14 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …
- range
UNIQUE(FILTER(A2:A15,A2:A15<>"")){5 rows × 1 col} "Finance", "Sales", "IT", "Marketing", …UNIQUE: Distinct rows of a range. Spills.- array
FILTER(A2:A15,A2:A15<>""){8 rows × 1 col} "Finance", "Sales", "IT", "Sales", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(FILTER(A2:A15,A2:A15<>"")). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =UNIQUE(TOCOL(A2:A15,1)) | spills 5 × 1 | TOCOL with 1 drops the blanks as it reads the range. Shorter, and it also works on a block of several columns. |
| =UNIQUE(A2:A15) | spills 6 × 1Different result | The plain version, to see the problem. Has an extra 0 after Sales, where the first blank appears. |
Common mistakes
- Formatting the 0 away with a custom number format hides it, but it still counts:
COUNTAof the list is one too high. - Cells that hold an empty string from a formula pass a TOCOL blank check but not
<>"". Use FILTER when the column is made of formulas. - Cells holding a space aren't empty. Use
TRIM(A2:A15)<>""as the condition if your data has them.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does UNIQUE return 0 for blank cells
C2, list each department from A2:A15 once, without a 0 or a blank for the empty cells.Questions
Why does a referenced blank cell show 0?
A formula that points at an empty cell returns 0 in Excel. UNIQUE and FILTER results behave the same way.
How do I also sort the list?
Wrap it: =SORT(UNIQUE(FILTER(A2:A15,A2:A15<>""))).
Does Google Sheets show 0 too?
Google Sheets shows an empty row instead, and the same FILTER formula removes it.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.