Excel UNIQUE, FILTER, and SORT Complete Guide: Remove Duplicates, Filter, and Sort in 3 Minutes
With a dynamic-array-based UNIQUE, FILTER, and SORT combination, you can automatically create an analysis table without modifying the source data. Copy and paste the examples below to reproduce the same results.
Quick Fix: 3-Minute Pipeline
- Convert the source data to a table with Ctrl+T (Sales).
- Create a unique list with UNIQUE:
=SORT(UNIQUE(Sales[Category])) - Extract rows with criteria using FILTER:
=FILTER(Sales,(Sales[Channel]="Online")*(Sales[Date]>=DATE(2025,8,26))) - Sort by revenue in descending order with SORTBY:
=SORTBY(FILTER(...), Sales[Revenue], -1)
Concepts: Function Roles and Combination Order
- UNIQUE removes duplicates, FILTER extracts rows that meet criteria, and SORT/SORTBY sorts data (use SORTBY for multiple criteria).
- List → UNIQUE → SORT / Extract rows → FILTER → SORTBY
Practical Example (Copy and Paste)
| Date | Channel | Category | Item | Qty | Revenue |
|---|---|---|---|---|---|
| 2025-08-25 | Online | Tops | T001 | 3 | 90000 |
| 2025-08-25 | Store | Bottoms | B014 | 1 | 35000 |
| 2025-08-26 | Online | Tops | T002 | 2 | 65000 |
| 2025-08-26 | Online | Shoes | S101 | 1 | 120000 |
| 2025-08-27 | Store | Shoes | S102 | 2 | 240000 |
| 2025-08-28 | Online | Outer | O010 | 1 | 159000 |
| 2025-08-29 | Online | Tops | T003 | 5 | 165000 |
| 2025-08-30 | Store | Outer | O011 | 1 | 199000 |
| 2025-08-30 | Online | Bottoms | B021 | 2 | 78000 |
| 2025-08-31 | Online | Shoes | S201 | 1 | 140000 |
Unique Category List
=SORT(UNIQUE(Sales[Category]))Online and Date Range Filter
=FILTER(Sales, (Sales[Channel]="Online")*(Sales[Date]>=DATE(2025,8,26)))Sort Revenue in Descending Order
=SORTBY(FILTER(Sales,(Sales[Channel]="Online")*(Sales[Date]>=DATE(2025,8,26))), Sales[Revenue], -1)Partial Match and Exclusion Term
=LET(k,"tops", ex,"T003", r,Sales, cond1,ISNUMBER(SEARCH(k,Sales[Category])), cond2,NOT(ISNUMBER(SEARCH(ex,Sales[Item]))), FILTER(r,cond1*cond2))Category Totals
=LET(cats,UNIQUE(Sales[Category]), sums, MAP(cats, LAMBDA(c, SUM(FILTER(Sales[Revenue], Sales[Category]=c)))), HSTACK(cats, sums))Top N Categories
=LET(cats,UNIQUE(Sales[Category]), sums, MAP(cats,LAMBDA(c,SUM(FILTER(Sales[Revenue],Sales[Category]=c)))), ranktbl, SORTBY(HSTACK(cats,sums), INDEX(HSTACK(cats,sums),,2), -1), TAKE(ranktbl,3))Sort by Multiple Criteria
=SORTBY(Sales, Sales[Revenue], -1, Sales[Qty], 1)Report with Only the Required Columns
=CHOOSECOLS(SORTBY(FILTER(Sales,Sales[Channel]="Online"), Sales[Revenue], -1), 1,3,4,6)LAMBDA(TopNByRevenue)
=LAMBDA(tbl, n, LET(cats,UNIQUE(INDEX(tbl,,3)), sums, MAP(cats,LAMBDA(c,SUM(FILTER(INDEX(tbl,,6), INDEX(tbl,,3)=c)))), ranktbl, SORTBY(HSTACK(cats,sums), INDEX(HSTACK(cats,sums),,2), -1), TAKE(ranktbl, n)))Alternatives, Notes, and Checklist
- For large datasets and formal ETL, Power Query/Power Pivot is recommended.
- Combine criteria with: AND = *, OR = +, NOT().
- For nonbreaking spaces, use
SUBSTITUTE(CHAR(160)," ")+TRIM. - If results conflict (#CALC!), clear the spill range.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| #CALC! spill error | Output range conflict | Move the formula to an empty area |
| New rows are not included | Regular range reference | Convert the data to a table and use column-name references |
| Sort priority is incorrect | Using SORT with one criterion | Use SORTBY with multiple criteria |
| Search results are missing | Spaces or nonbreaking spaces | Use TRIM/SUBSTITUTE |
Conclusion
The UNIQUE, FILTER, and SORT pipeline alone can automatically refresh an analysis table while preserving the source data. The next article combines it with VSTACK and WRAPROWS to complete chart automation.