The UNIQUE guide
=UNIQUE(B2:B13) lists each value in B2:B13 once, in the order it first appears. You write it in one cell and Excel fills, or spills, the list into the empty cells below. It works in Excel 2021, Excel for Microsoft 365 and Excel for the web.
The syntax
=UNIQUE(array, [by_col], [exactly_once])array: the range to take values from, without the header.by_col: TRUE compares columns instead of rows. Leave it out for a normal list.exactly_once: TRUE returns only the values that appear once, which is a different question ("which customers ordered only once?").
UNIQUE ignores case, so "Marlow" and "marlow" count as one. It doesn't ignore spaces: "Marlow " with a space at the end is a separate value, so clean the column with TRIM first.
Spilling, and #SPILL!
A formula that returns a list fills as many cells as the list needs. Write it once, in the top cell, and leave the cells below empty. Click any cell in the list and Excel outlines the whole spilled range; the formula bar shows the formula greyed out in every cell but the first.
If one of those cells has anything in it, even a space, the formula shows #SPILL! instead of the list. Clear the cell in the way and the list appears. Filling the formula down by habit causes the same thing, because each copy blocks the one above.
Totals next to the list
With the distinct list in E2:E6, a total for each customer is =SUMIF($B$2:$B$13, E2, $C$2:$C$13), filled down. E2# means "the whole list that spills from E2": =COUNTA(E2#) counts it, and it grows with the list. In Excel 365 you can also write =SUMIF(B2:B13, E2#, C2:C13) once and the totals spill too; this practice sheet spills UNIQUE only, so fill SUMIF down here. A pivot table gives the same summary without formulas, but it needs a refresh when the data changes.
Count distinct values
=COUNTA(UNIQUE(B2:B13)) counts the list without showing it. =ROWS(UNIQUE(B2:B13)) gives the same number. If the range has empty cells, UNIQUE returns one of them as a 0 and it gets counted; leave blanks out of the range or filter them first with FILTER(B2:B13, B2:B13<>"").
Without UNIQUE
In Excel 2019 and earlier, use Data → Remove Duplicates on a copy of the column, or a pivot table with the field in Rows. Both give a list that doesn't update by itself. =SORT(UNIQUE(B2:B13)) gives a sorted list in versions that have UNIQUE.
Common mistakes
- Including the header.
UNIQUE(B1:B13)puts "Customer" at the top of the list. - Filling the formula down. One formula in the top cell is enough.
- COUNT instead of COUNTA. COUNT counts numbers only, so a list of names comes out as 0.
- Stray spaces. "Halden" and "Halden " show up as two customers.
FAQ
- How do I get a list of unique values in Excel?
- Use =UNIQUE(range) in Excel 2021, Microsoft 365 or Excel for the web. The list spills into the cells below. In older versions, use Data > Remove Duplicates on a copy, or a pivot table.
- How do I count unique values in Excel?
- Use =COUNTA(UNIQUE(range)) or =ROWS(UNIQUE(range)). Leave empty cells out of the range, or UNIQUE returns one as 0 and it is counted.
- Why does UNIQUE show #SPILL!?
- The list needs empty cells below the formula, and one of them has something in it. Clear that cell, or move the formula to where there is room.
- Is UNIQUE case-sensitive?
- No. "North" and "north" count as the same value. Spaces do count, so a value with a trailing space appears twice; clean the column with TRIM first.
This task is in the timed data analyst and advanced Excel practice tests. What Excel interview tests check
