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.