Excel function reference

Array Formulas

Quick answer

UNIQUE, SORT, and FILTER: functions that return a whole list of values at once, not just one.

UNIQUE(array) · SORT(array, [sort_order]) · FILTER(array, include)

UNIQUE, SORT, and FILTER belong to Excel's "dynamic array" family: each one returns a whole list of values rather than a single answer. In real Excel, that list spills down and across as many cells as it needs starting from the formula cell. LearnExcel's practice grid grades one formula against one expected result per exercise, so here the computed list is checked as a whole rather than shown spilling across the sheet, but the underlying logic is identical either way.

These three also nest inside each other freely, which is where they get genuinely useful: FILTER can pick out the rows that matter, and SORT can then rank exactly that filtered subset, all in a single formula with no intermediate helper column.

Syntax

UNIQUE(array) · SORT(array, [sort_order]) · FILTER(array, include)

Argument	Description
array	Required for all three. The range (or the result of another array formula) to deduplicate, sort, or filter.
sort_order	Optional for SORT. 1 for ascending (the default), -1 for descending.
include	Required for FILTER. A range of TRUE/FALSE values, usually built from a comparison like B2:B6>100, one per row of array.

A real example: the biggest orders over a threshold, ranked

FILTER picks out only the amounts that clear a minimum, then SORT ranks exactly that filtered list, descending, in one nested formula:

=SORT(FILTER(A2:A4,A2:A4>100),-1)

Nothing below the threshold ever reaches SORT; it only ever sees the subset FILTER already decided belonged in the result.

Worked examples

Removing duplicates

	A
1	Dept
2	Sales
3	Sales
4	Marketing
5	Sales

=UNIQUE(A2:A5) → Sales, Marketing

(first-seen order, not alphabetical)

Sorting descending

	A
1	Score
2	72
3	95
4	63

=SORT(A2:A4,-1) → 95, 72, 63

Filtering, then sorting

	A
1	Amount
2	120
3	45
4	300

=SORT(FILTER(A2:A4,A2:A4>100),-1) → 300, 120

(45 is filtered out before the sort ever runs)

Common mistakes

Writing a bare range comparison, like B2:B6>100, as a whole argument outside of FILTER. It's only meaningful as FILTER's include condition, not as a standalone formula result.
Assuming UNIQUE sorts its output. It doesn't; it keeps first-seen order, so pair it with SORT if you need both.
Forgetting FILTER errors out when nothing matches. Always sanity-check that the condition can actually match something in the data.

Related functions

Try it yourself

A free, graded practice exercise using this exact function: instant feedback, no signup required.

Practice array formulas →