FILTER, SORT & UNIQUE
Build useful live lists by selecting records, ordering them and removing repeated entries.
By the end, you’ll be able to:
- Return every record that meets a condition
- Combine selection and sorting without separating records
- Create distinct lists and understand exactly-once behavior
Give each function one clear job
FILTER chooses qualifying rows. SORT orders rows. UNIQUE removes repeated rows or entries from a result. Combining them can create a short live view without changing the source data.
Our order log has East sales of 120, 200 and 60. =FILTER(A2:C6,B2:B6="East","No orders") returns the three complete East records. Every field stays attached to its order ID. Filtering only the Sales column would return values without their context.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Sales |
| 2 | R101 | East | 120 |
| 3 | R102 | West | 80 |
| 4 | R103 | East | 200 |
| 5 | R104 | West | 150 |
| 6 | R105 | East | 60 |
Select rows with FILTER
=FILTER(array, include, [if_empty])array is the data to return. include is a corresponding true/false row or column mask. if_empty is a deliberate fallback when nothing qualifies. Without it, no matches produce #CALC! rather than an empty Excel array.
=FILTER(A2:C6,(B2:B6="East")*(C2:C6>=100),"No orders") returns R101 and R103. Multiplication implements AND. Addition can implement OR; the include mask treats nonzero as true, so a row meeting both conditions is still returned only once.
The mask height must match the number of source rows. Source and criteria starting on different rows can give incorrect selections.
Order the selected rows
=SORT(array, [sort_index], [sort_order], [by_col])array is the result to sort. sort_index identifies a column within it, default 1. sort_order is 1 ascending or -1 descending, default 1. by_col defaults to FALSE for sorting rows; TRUE sorts columns.
Wrap the East view in =SORT(FILTER(A2:C6,B2:B6="East","No orders"),3,-1) to put Sales largest first. The sort index is 3 because Sales is the third column in the returned rectangle.
For an empty multi-column result, a text fallback does not have three columns. Use a design that handles that case before applying a third-column sort; do not assume every nested example works on an empty source.
Distinct is different from exactly once
=UNIQUE(array, [by_col], [exactly_once])array is the source. by_col defaults to FALSE, comparing rows; TRUE compares columns. exactly_once defaults to FALSE, keeping one representative of each distinct row. TRUE returns only rows that occur once.
=SORT(UNIQUE(B2:B6)) returns East and West. Both occur repeatedly, but each is represented once. UNIQUE with exactly_once TRUE would return no region here, because neither occurs only once. UNIQUE across A2:C6 compares entire records, not Region alone.
Build and check a useful view
Excel versions: Dynamic arrays, FILTER, SORT, UNIQUE, XLOOKUP and LET are available in Microsoft 365, Excel 2021 and Excel 2024, including supported Mac versions. LAMBDA requires Microsoft 365 or Excel 2024. The browser exercises work without installing Excel; they implement the lesson's stated subset rather than the complete Excel calculation engine.
- Write FILTER on its own and verify the selected IDs.
- Add SORT and check the sort column is relative to the returned array.
- Use UNIQUE only at the intended level, such as a list of regions.
- Test no matches, one match and several matches.
- Leave output space clear and label the selection rule.
Our exercises check bounded row-filtering, sorting by one column and distinct lists. They do not emulate every spill, column-sorting or external-workbook behavior.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The practice checker accepts any supported formula that solves the task, including whole-column ranges where supported, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
FILTER selects, SORT orders and UNIQUE deduplicates at the level you specify. Test empty results and preserve the connection between each record’s fields.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Technical review: 2026-10-16. Page updated: 2026-10-19. How SheetSprout develops and checks lessons.