3.3 Dynamic arrays and spill formulas
One formula returns a whole list that resizes itself.
Before:02. Excel — Formulas & FunctionsUnlocks:05. Excel — Charts & Dashboards
A modern Excel formula can return a whole list that resizes itself, spilling into neighbouring cells, and this topic covers FILTER, SORT, UNIQUE and SEQUENCE, the functions that make that useful. It sits in the analysis module because these functions replace whole sheets of helper columns. The SPILL error is the confusion to expect: it just means something is sitting in the cells the result needs. The other adjustment is that only the top-left cell holds the formula; the rest of the spill is a shadow of it and cannot be edited directly.
Work through these
The spill range and the # operator
One formula can return many values, filling the cells below and to the right, and there is notation for referring to the whole result. This changed how modern spreadsheets are built.
FILTER, SORT, SORTBY, UNIQUE, SEQUENCE
Filtering, sorting, extracting distinct values and generating sequences, all as formulas that update themselves. These five between them replace an enormous amount of manual work.
#SPILL! and what is in the way
When a result cannot fill the space it needs, the formula reports it rather than overwriting anything. The fix is always clearing whatever is in the way.
Replacing helper columns with a single formula
Many workbooks are full of helper columns that exist only to feed one final formula. This item is about collapsing those into a single expression that is easier to check.
Practice file
- xl-07-dashboard-starter.xlsx
Clean data plus an empty dashboard sheet with the five questions written on it.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.