Course contents · Excel functions

Dynamic arrays: FILTER, SORT, UNIQUE

Open the practice in the trainer →

In Excel for Microsoft 365 and Excel 2021 one formula can return many values — they "spill" into the neighbouring cells down and to the right. The spill range has a blue border; only the first cell can be edited. If the space is occupied you get a #SPILL! error instead: clear those cells.

UNIQUE(range)a list without duplicates
FILTER(array, include, [if_empty])the rows where the condition is true: =FILTER(A2:C11, C2:C11>1000)
SORT(array, [column], [1 or -1])a sorted copy; the source data stays as it is
SORTBY(array, by_array, [order])sorting by another column
SEQUENCE(rows, [columns], [start], [step])a sequence of numbers: =SEQUENCE(10) — 1…10
LET(name, value, …, calculation)variables inside a formula: a value is calculated once and the formula reads better

The reference E2# means "the whole array spilled from E2" — it grows and shrinks by itself: =COUNTA(E2#). Plain range operations spill too: =B2:B11*C2:C11 returns a column of products. You no longer need Ctrl+Shift+Enter as for old array formulas.

Tasks in the trainer

  1. In E2 list the regions without duplicates with one formula.
  2. In G2 — all orders over 1000 (manager, region, amount).
  3. In K2 — the same table sorted by amount, largest first.
  4. In O2 number the rows from 1 to 10 with one formula.
  5. In Q2 — each amount as a share of the total; calculate the total once with LET.
  6. In E7 count the regions in the list with the spill reference E2#.

Solve the tasks in the trainer →

Check yourself

  1. What does the #SPILL! error mean?
  2. What does the reference E2# mean?