The formula
Excel & Google SheetsExcelGoogle Sheets
=SORT(UNIQUE(A2:A9))In C2, spills: [Folders], [Ink], [Paper], [Pens]
Count of unique itemsExcelGoogle Sheets
=COUNTA(UNIQUE(A2:A9))In E2 → 4
Ignore blank cellsExcel
=SORT(UNIQUE(FILTER(A2:A9,A2:A9<>"")))In F2, spills: [Folders], [Ink], [Paper], [Pens]
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | |||||
| 2 | Pens | Folders | 4 | Folders | ||
| 3 | Ink | Ink | Ink | |||
| 4 | Paper | Paper | Paper | |||
| 5 | Pens | Pens | Pens | |||
| 6 | Folders | |||||
| 7 | Ink | |||||
| 8 | Pens | |||||
| 9 | Paper |
How it works
- UNIQUE(A2:A9)
- Each distinct value once, in order of first appearance.
- SORT(…)
- Sorts the list A–Z. Use SORT(…,1,-1) for Z–A.
- FILTER(A2:A9,A2:A9<>"")
- Drops empty cells first so you don't get a 0 or blank in the list.
Common errors and fixes
Why does the formula return #SPILL!?
Something is in the cells the list needs. Clear the area below the formula.
A 0 appears in the list?
The range includes empty cells. Use the FILTER version.
Why does the formula return #NAME??
Excel 2019 or older doesn't have UNIQUE. See remove duplicates with a formula.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.