# Why does UNIQUE return 0 for blank cells in Excel

UNIQUE treats an empty cell as a value and shows it as 0. Remove the blanks first: `=UNIQUE(FILTER(A2:A15,A2:A15<>""))`.

**Excel:** `=UNIQUE(FILTER(A2:A15,A2:A15<>""))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Department |  | Departments |
| Finance |  |  |
| Sales |  |  |
|  |  |  |
| IT |  |  |
| Sales |  |  |
|  |  |  |
| Marketing |  |  |
| Finance |  |  |
|  |  |  |
| IT |  |  |
| Legal |  |  |
|  |  |  |
|  |  |  |
|  |  |  |

Formula in C2: `=UNIQUE(FILTER(A2:A15,A2:A15<>""))`

Result (spills 5 × 1): Finance; Sales; IT; Marketing; Legal

## 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.

## Other ways

- `=UNIQUE(TOCOL(A2:A15,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)`: 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: `COUNTA` of 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/unique-returns-zero-for-blank-cells