Get Unique Values from a List in Excel and Google Sheets
Purpose
Extracting a unique list from a column of repeated values is one of the most common data-preparation tasks I do.…
Extracting a unique list from a column of repeated values is one of the most common data-preparation tasks I do. Build a dropdown list of regions from a sales log. Get a distinct list of product categories from an order table. Find every unique customer name from a billing column that repeats names across hundreds of rows. The UNIQUE function does all of this in a single formula that spills its results automatically and stays live as data changes. Before UNIQUE arrived, this required either the Data > Remove Duplicates tool (which modifies the original data) or a SUMPRODUCT workaround that broke when the range contained blanks. Now the pattern is simple: UNIQUE gives you the distinct list, wrapping it in SORT gives you a sorted distinct list, and wrapping it in COUNTA gives you the count. These three combinations cover every unique-value need in modern Excel and Google Sheets.
Syntax
✓ Excel 365 / 2021
✓ Google Sheets
= Same syntax
=UNIQUE(array)
For older Excel without UNIQUE, use Data > Remove Duplicates or a SUMPRODUCT workaround.
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The range or array to deduplicate. A single column returns unique values from that column. A multi-column range returns unique row combinations. |
| by_col | Optional | FALSE (default) to return unique rows. TRUE to return unique columns. |
| exactly_once | Optional | FALSE (default) returns one instance of every distinct value. TRUE returns only values that appear exactly one time. |
How it works
UNIQUE scans the array argument and returns each distinct value in the order it first appears, spilling the results into adjacent empty cells automatically. The spill range expands or contracts as values are added to or removed from the source. Text comparison is case-insensitive. Blank cells are treated as a value -- if any blank exists in the range, one blank appears in the output. Wrap in FILTER to exclude blanks before deduplication: UNIQUE(FILTER(range,range<>"")). Wrap in SORT for alphabetical ordering: SORT(UNIQUE(range)). Count the distinct values with COUNTA(UNIQUE(range)). For Excel 2019 and earlier where UNIQUE does not exist, the two practical workarounds are: Data > Remove Duplicates on a copy of the column for a one-time static unique list, or the legacy SUMPRODUCT formula =SUMPRODUCT(1/COUNTIF(range,range)) to count unique values without extracting them.
Examples
1
Extract a unique list of regions from a sales column that contains many repeated entries.
| A | B | |
|---|---|---|
| 1 | Rep | Region |
| 2 | Alice | North |
| 3 | Bob | South |
| 4 | Carol | East |
| 5 | Dan | North |
| 6 | Ella | South |
| 7 | Finn | North |
| 8 | Gia | East |
| 9 | Hugo | North |
| 10 | Iris | South |
| 11 | Jo | East |
Row 2: North
— Three distinct regions exist: North, South, and East -- returned in the order they first appear. UNIQUE spills all three into the formula cell and the two cells below it.
North appears 4 times and South and East each appear 3 times. UNIQUE returns each exactly once. The formula cell and the two cells below must be empty or a #SPILL! error appears.
2
Return a sorted alphabetical list of unique product categories from an order log using SORT wrapped around UNIQUE.
| A | B | |
|---|---|---|
| 1 | Order | Category |
| 2 | ORD-001 | Electronics |
| 3 | ORD-002 | Accessories |
| 4 | ORD-003 | Electronics |
| 5 | ORD-004 | Furniture |
| 6 | ORD-005 | Accessories |
| 7 | ORD-006 | Furniture |
| 8 | ORD-007 | Electronics |
| 9 | ORD-008 | Stationery |
| 10 | ORD-009 | Accessories |
| 11 | ORD-010 | Electronics |
Row 2: Accessories
— Four distinct categories: Accessories, Electronics, Furniture, Stationery -- returned in alphabetical order by SORT. Without SORT, UNIQUE would return Electronics first as it appears first in the data.
SORT(UNIQUE(range)) is the cleanest single-formula way to produce a sorted deduplicated list. The result updates automatically whenever the source category column gains new entries or new values.
3
Count distinct customers in an order column using COUNTA(UNIQUE(...)), and compare with the legacy SUMPRODUCT workaround for older Excel.
| A | ||
|---|---|---|
| 1 | Customer | |
| 2 | Alice | |
| 3 | Bob | |
| 4 | Carol | |
| 5 | Alice | |
| 6 | Dan | |
| 7 | Bob | |
| 8 | Eve | |
| 9 | Carol | |
| 10 | Alice | |
| 11 | Dan | |
| 12 | Modern count | =COUNTA(UNIQUE(A2:A11)) |
| 13 | Legacy count | =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)) |
Row 2: 5
— Five distinct customers: Alice, Bob, Carol, Dan, Eve. COUNTA(UNIQUE(...)) returns 5. The legacy SUMPRODUCT formula also returns 5 and works in any Excel version.
The SUMPRODUCT formula =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)) works in Excel 2003 and later but breaks if any cell in the range is blank -- the division by zero from COUNTIF returns an error. For ranges that may contain blanks, use SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)*(A2:A11<>"")).
Common use cases
1. Build a live unique list of regions, departments, or categories from a column that grows over time
=UNIQUE(B2:B1000)
2. Create a sorted alphabetical dropdown source list from a column of repeated values
=SORT(UNIQUE(A2:A500))
3. Count how many distinct customers or products appear in an order log
=COUNTA(UNIQUE(A2:A1000))
4. Remove blank cells from the unique list by filtering before deduplicating
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
5. Count unique values in older Excel without UNIQUE using SUMPRODUCT
=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))
Common errors
#SPILL! error
The cells below the formula are not empty. UNIQUE cannot spill results into occupied cells.
Fix: Clear the cells in the spill range. UNIQUE needs as many empty cells below the formula as there are distinct values in the source range.
Blank appears in the unique list
The source range contains blank cells and UNIQUE treats a blank as a valid value, including one blank in the output.
Fix: Wrap in FILTER before UNIQUE: =UNIQUE(FILTER(A2:A100,A2:A100<>"")) to exclude blanks before deduplication.
#NAME? error
UNIQUE is not available in Excel 2019 or earlier.
Fix: Use Excel 365 or Excel 2021. For older Excel, use Data > Remove Duplicates for a static result, or the SUMPRODUCT formula for counting unique values.
Tips and variations
Use SORT(UNIQUE(range)) as the default unique list formula
The unsorted order from UNIQUE alone reflects first-appearance order which is rarely what a report or dropdown needs. Adding SORT gives a predictable alphabetical list that is easier to read and navigate. This is the pattern I use for every dynamic dropdown source.
=SORT(UNIQUE(A2:A1000))
Use UNIQUE as a data validation source with the spill reference operator
Enter =UNIQUE(A2:A100) in cell E1 and reference E1# in a data validation list source. The # operator references the entire spill range so the dropdown grows automatically as new unique values appear.
=UNIQUE(A2:A100)
Combine with FILTER to exclude blanks and specific values
=UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(A2:A100<>"N/A"))) returns unique values while excluding both blanks and N/A entries. The multiplication combines AND conditions across the FILTER include argument.
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
Related reading
Frequently asked questions
In Excel 365 and Excel 2021, use =UNIQUE(A2:A100) which returns a dynamic list of distinct values that spills automatically into adjacent cells. In older Excel versions without UNIQUE, use =IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$100),0)),"") entered as an array formula, or use Data > Remove Duplicates for a one-time static result. Google Sheets supports UNIQUE natively in all versions.
Wrap UNIQUE inside SORT: =SORT(UNIQUE(A2:A100)) returns all distinct values in alphabetical order. The result spills automatically and updates whenever the source data changes. This replaces the two-step workflow of removing duplicates then sorting, and unlike those manual steps it stays live.
In Excel 365 and Google Sheets, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)). In older Excel without UNIQUE, use the SUMPRODUCT formula: =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) for a range with no blanks, or =SUMPRODUCT((1/COUNTIF(A2:A100,A2:A100))*(A2:A100<>"")) when blanks may be present.
No -- by default UNIQUE treats a blank cell as a value and will include one blank in the unique list if any blanks exist in the source range. To exclude blanks, wrap UNIQUE in FILTER first: =UNIQUE(FILTER(A2:A100,A2:A100<>"")) returns only the non-blank unique values.
No. UNIQUE is only available in Excel 365 and Excel 2021. It is not in Excel 2019, 2016, or earlier. For older Excel, the standard workarounds are: Data > Remove Duplicates for a one-time static unique list, a pivot table for a grouped unique list, or an array-formula-based INDEX MATCH approach for a dynamic result. UNIQUE is available in all versions of Google Sheets.
Related Functions