The most significant change to Excel formulas in 30 years — and why you need to learn them now.
Before dynamic arrays (introduced in Excel 365), returning multiple values from one formula required Ctrl+Shift+Enter array formulas — complex, fragile, and confusing. Dynamic arrays changed everything: write one formula, it automatically spills results into as many cells as needed.
When a formula returns multiple values, they spill into adjacent cells automatically. A blue border appears around the spill range. Reference the whole spill range with a # after the first cell: =A2# refers to everything that spilled from A2.
A #SPILL! error means something is blocking the output range. Clear the cells where the formula needs to spill.
=FILTER(array, include, [if_empty])
=FILTER(A2:C100, B2:B100="North", "No results")=SORT(array, [sort_index], [sort_order])
=SORT(A2:C50, 3, -1) sort by column 3, descending=SORTBY(array, by_array, [sort_order])
=SORTBY(A2:A20, C2:C20, -1) sort names by scores in column C=UNIQUE(array)
=UNIQUE(A2:A100) returns each distinct value once=SEQUENCE(rows, [columns], [start], [step])
=SEQUENCE(10) 1 through 10
=SEQUENCE(5, 3) a 5×3 grid of sequential numbers=RANDARRAY(rows, columns, min, max, integer)
=RANDARRAY(10, 1, 1, 100, TRUE) 10 random integers between 1 and 100=SORT(UNIQUE(A2:A100))
-- Sorted list of unique values
=SORT(FILTER(A2:C100, B2:B100="North"), 3, -1)
-- Filter to North, then sort by column 3 descendingExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.
Start practising free →