Streamline Data Preparation with FILTER, SORT, and UNIQUE (Conditional Filtering, Multi-Level Sorting, and Deduplication in 1 Minute)
Automate your pre-PivotTable data preparation pipeline with a single formula. This article covers OR/AND logic, date ranges, custom sorting, and unique lists.
Editorial review notice: This article was reproduced and verified by an editor in Excel 365. AI tools were used as a supplementary aid during initial draft preparation.
Syntax and Quick Start
| Function | Syntax | Key point |
|---|---|---|
| FILTER | =FILTER(array, include, [if_empty]) | TRUE/FALSE array for the condition |
| SORT | =SORT(array,[sort_index],[sort_order],[by_col]) | Simple sorting by a single column |
| SORTBY | =SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2]…) | Detailed control by column |
| UNIQUE | =UNIQUE(array,[by_col],[exactly_once]) | Unique rows or columns; values that occur only once |
// Example: Top 20 sales records in descending order for Seoul in 2025
=TAKE(SORTBY(FILTER(A2:F10000,
(YEAR(Date)=2025)*(Region="Seoul")), Sales, -1), 20)FILTER — Condition Formula Recipes
AND / OR / NOT
// AND: multiplication (*)
=FILTER(Data, (Region="Seoul")*(Category="Keyboard"))
// OR: addition (+)
=FILTER(Data, (Region="Seoul")+(Region="Busan"))
// NOT: negation
=FILTER(Data, Category<>"Returns")Partial Matches/Wildcards
=FILTER(Data, ISNUMBER(SEARCH("pro", LOWER(Product))))Date Range (Safe When Times Are Included)
=FILTER(Data, (Date>=&DATE(2025,1,1))*(Date<EOMONTH(DATE(2025,1,1),2)+1))Exclude Missing Values
=FILTER(Data, (Value<>"")*(Value<>#N/A))SORT / SORTBY — Multi-Level Sorting
Single Key
=SORT(Data, 4, -1) // Descending by the fourth columnMultiple Keys
=SORTBY(Data, Region, 1, Sales, -1)Custom Order (Label Priority)
=SORTBY(Data, XLOOKUP(Priority, {"High","Medium","Low"}, {1,2,3}), 1, Date, -1)UNIQUE — Unique Lists and Counts
Unique List
=UNIQUE(Category)Unique Count
=ROWS(UNIQUE(CustomerID))Values That Appear Only Once
=UNIQUE(Category,, TRUE) // exactly_once=TRUEPipeline Combinations
// 1) Filter → Sort → Remove duplicates
=UNIQUE(SORTBY(FILTER(A2:D, Dept="Sales"), Date, -1))
// 2) Most recent order for each unique customer
=LET(c, UNIQUE(Customer),
latest, MAP(c, LAMBDA(x, MAX(FILTER(Date, Customer=x)))),
HSTACK(c, latest))12 Practical Patterns
① Top 10 Sales Records from the Past 30 Days
=TAKE(SORTBY(FILTER(Data, Date>=TODAY()-30), Sales, -1), 10)② Create Source Data for a Region-by-Month Cross-Tabulation
=LET(m, EOMONTH(Date,0), r, UNIQUE(Region),
MAKEARRAY(ROWS(r), ROWS(UNIQUE(m)),
LAMBDA(i,j, SUM(FILTER(Sales, (Region=INDEX(r,i))*(m=INDEX(UNIQUE(m),j)))))))③ Repeat Customers Only
=FILTER(Data, COUNTIF(Customer, Customer)>1)④ Exclude Out-of-Stock Items and Sort by Inventory Ascending
=SORTBY(FILTER(Data, Stock>0), Stock, 1)⑤ OR Search Across Multiple Keywords
=FILTER(Data, BYROW(Product, LAMBDA(r, SUM(--ISNUMBER(SEARCH(TRANSPOSE(Keywords), r)))>0 )))⑥ Latest Status by Owner
=LET(u, UNIQUE(Owner),
HSTACK(u, MAP(u, LAMBDA(x, TAKE(SORTBY(FILTER(Data, Owner=x), Date, -1),1)))))⑦ Orders That Appear Exactly Once
=FILTER(Data, COUNTIF(OrderID, OrderID)=1)⑧ Unique Customer List and First Purchase Date
=LET(c, UNIQUE(Customer), HSTACK(c, MAP(c, LAMBDA(x, MIN(FILTER(Date, Customer=x))))))⑨ Top N% of Sales Only
=LET(t, LARGE(Sales, ROUNDUP(COUNTA(Sales)*0.2,0)),
FILTER(Data, Sales>=t))⑩ Priority (Custom Order) → Date Descending
=SORTBY(Data, XLOOKUP(Priority,{"P0","P1","P2","P3"},{0,1,2,3}), 1, Date, -1)⑪ Unique Headcount by Department
=LET(d, UNIQUE(Dept), HSTACK(d, MAP(d, LAMBDA(x, ROWS(UNIQUE(FILTER(Name, Dept=x)))))))⑫ Display a Message When There Are No Filter Results
=IFERROR(FILTER(Data, Region="Mars"), "No data matches the criteria")Alternatives for Older Versions (Without FILTER/SORT/UNIQUE)
// FILTER ≈ INDEX/SMALL/IF (array formula; regular formula in Microsoft 365)
=IFERROR(INDEX(A:A, SMALL(IF(조건, ROW(A:A)), ROWS($A$1:A1))), "")
// SORT ≈ SORTBY alternative: Create keys in a helper column and rearrange with INDEX
=INDEX(Data, MATCH(SMALL(키, ROWS($A$1:A1)), 키, 0), )
// UNIQUE ≈ Advanced Filter or PivotTable → unique valuesTIP For files you distribute, create an Excel table (CTRL+T) and write formulas using column names for greater stability.
Common Mistakes and Checks
- #SPILL! → Check whether the spill range is clear (remove merged cells, notes, and hidden columns).
- Data types → When dates, numbers, and text are mixed, normalize them with NUMBERVALUE/DATEVALUE.
- Expected OR logic → Use
+for OR in FILTER. Use*for AND. - Performance → For large sheets, cache repeated calculations with LET and limit ranges to table columns.
Summary
| Goal | Typical Formula |
|---|---|
| Conditional filtering | FILTER(array, condition) |
| Multi-level sorting | SORTBY(array, key1,sort, key2,sort) |
| Deduplication/count | UNIQUE(array) / ROWS(UNIQUE(...)) |
| Pipeline | FILTER → SORTBY → UNIQUE |