Array Formulasintermediate
Filter rows by a condition with FILTER
=FILTER(A2:C100,B2:B100="East","No results")
Excel how-to guide
How to unique deduplicate list in Excel
Create a de-duplicated list of product names, customers, or categories from a raw data column to use as a dropdown source or summary label.
Step-by-step instructions
How to unique deduplicate list — 3 steps
1
Click on the cell where you want the unique list to begin — UNIQUE will spill downward automatically.
2
Reference the range containing the values you want to de-duplicate.
3
Leave the optional second and third arguments blank for the most common use case of distinct rows in a single column.
Example data
Worked example
| Category |
|---|
| Electronics |
| Clothing |
| Electronics |
| Books |
| Clothing |
| Books |
Common mistakes
Errors to watch out for
UNIQUE is not available
UNIQUE requires Excel 365 or Excel 2021. It is not available in Excel 2019 or earlier. For older versions, use Remove Duplicates from the Data menu or an array formula workaround.
SPILL error
A #SPILL error means cells below the formula are not empty. Clear the range where results need to spill and the formula will resolve automatically.
FAQ
Frequently asked questions
How do I sort the unique list alphabetically?
Wrap in SORT — =SORT(UNIQUE(A2:A100)) — to return the distinct values in ascending order.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Array Formulasbeginner
Sort a range dynamically with SORT
=SORT(A2:B20,2,-1)