Excel array formulas (UNIQUE, FILTER, SORT) practice exercises
5 graded problems with realistic data, from medium to hard.
UNIQUE, SORT, and FILTER: functions that return a whole list of values at once, not just one. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the Array formulas (UNIQUE, FILTER, SORT) guide first: syntax, a worked example and the mistakes people make.
In this set
- Get the distinct list of departments
- Rank scores from highest to lowest
- Alphabetize a guest list
- Pull out only the large orders
- Filter, then rank the results
The exercises
A roster lists every employee's department, with plenty of repeats. Pull out just the distinct department names.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | ||
| 2 | Maya | Sales | ? | |
| 3 | Owen | Sales | ||
| 4 | Priya | Marketing | ||
| 5 | Noah | Sales | ||
| 6 | Lena | Marketing | ||
| 7 | Sam | IT |
Enter a formula in D2.
Show a hint
UNIQUE takes one range and drops every repeat, keeping the first occurrence of each: =UNIQUE(B2:B7).
Getting the distinct values out of a column, like departments, customers, or product codes, used to mean a pivot table or Remove Duplicates. UNIQUE does it in one formula.
Five rounds of a competition came in out of order. Sort the scores from highest to lowest so it's easy to read off the ranking.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Round | Score | ||
| 2 | 1 | 72 | ? | |
| 3 | 2 | 95 | ||
| 4 | 3 | 63 | ||
| 5 | 4 | 88 | ||
| 6 | 5 | 81 |
Enter a formula in D2.
Show a hint
SORT's second argument is the sort order, 1 for ascending (the default) or -1 for descending: =SORT(B2:B6,-1).
SORT reorders a range live, without touching the original data or reaching for the Data tab. It's useful anywhere the order itself is the point, like a leaderboard.
A guest list was entered in the order people RSVP'd. Put the names in alphabetical order for the printed seating chart.
| A | B | C | |
|---|---|---|---|
| 1 | Guest | ||
| 2 | Priya | ? | |
| 3 | Marco | ||
| 4 | Aiko | ||
| 5 | Daniel | ||
| 6 | Fatima |
Enter a formula in C2.
Show a hint
SORT defaults to ascending order when you leave the second argument off: =SORT(A2:A6).
Text sorts alphabetically by default, the same as numbers sort smallest-first. SORT doesn't care whether the range holds numbers or names.
An order list mixes small and large amounts together. Pull out just the orders over $100, in the order they came in.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Amount | ||
| 2 | #901 | 120 | ? | |
| 3 | #902 | 45 | ||
| 4 | #903 | 300 | ||
| 5 | #904 | 88 | ||
| 6 | #905 | 210 | ||
| 7 | #906 | 60 |
Enter a formula in D2.
Show a hint
FILTER's second argument is a condition over the same range. A range compared with > produces one TRUE/FALSE per row: =FILTER(B2:B7,B2:B7>100).
FILTER pulls out only the rows that pass a test (the orders over $100) while leaving everything else out entirely, and it keeps the original row order rather than resorting anything.
Same order list as before, but now rank just the large orders (over $100) from biggest to smallest, in one step.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Amount | ||
| 2 | #901 | 120 | ? | |
| 3 | #902 | 45 | ||
| 4 | #903 | 300 | ||
| 5 | #904 | 88 | ||
| 6 | #905 | 210 | ||
| 7 | #906 | 60 |
Enter a formula in D2.
Show a hint
Nest one inside the other. FILTER picks the rows, then SORT ranks whatever FILTER returns: =SORT(FILTER(B2:B7,B2:B7>100),-1).
Array functions nest like any other formula. Filtering, then sorting the filtered result, is a one-line replacement for what used to take a helper column and a manual sort.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the Array formulas (UNIQUE, FILTER, SORT) exercises →