Excel practice · Dates & Arrays

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

  1. Get the distinct list of departments
  2. Rank scores from highest to lowest
  3. Alphabetize a guest list
  4. Pull out only the large orders
  5. Filter, then rank the results

The exercises

1. Get the distinct list of departments Medium

A roster lists every employee's department, with plenty of repeats. Pull out just the distinct department names.

ABCD
1EmployeeDepartment
2MayaSales?
3OwenSales
4PriyaMarketing
5NoahSales
6LenaMarketing
7SamIT

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.

2. Rank scores from highest to lowest Medium

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.

ABCD
1RoundScore
2172?
3295
4363
5488
6581

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.

3. Alphabetize a guest list Medium

A guest list was entered in the order people RSVP'd. Put the names in alphabetical order for the printed seating chart.

ABC
1Guest
2Priya?
3Marco
4Aiko
5Daniel
6Fatima

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.

4. Pull out only the large orders Medium

An order list mixes small and large amounts together. Pull out just the orders over $100, in the order they came in.

ABCD
1OrderAmount
2#901120?
3#90245
4#903300
5#90488
6#905210
7#90660

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.

5. Filter, then rank the results Hard

Same order list as before, but now rank just the large orders (over $100) from biggest to smallest, in one step.

ABCD
1OrderAmount
2#901120?
3#90245
4#903300
5#90488
6#905210
7#90660

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 →

More dates & arrays practice

All Excel practice exercises · Excel test prep