Dynamic Arrays
Executive Summary
Key Takeaways
- ✓ A dynamic array formula returns multiple results from a single formula cell, which Excel spills into the neighbouring cells automatically.
- ✓ SORT, FILTER, UNIQUE, and SEQUENCE are dynamic array functions; the spilled range is owned entirely by the formula in its top-left cell.
- ✓ A spilled range behaves differently from a static range of individually entered formulas when it comes to how other formulas reference it, which matters for a model's audit trail.
Definition¶
A dynamic array formula is a single Excel formula that returns more than one result, which Excel automatically places into the neighbouring cells below and to the right of the formula cell — a behaviour Microsoft calls "spilling." The formula exists only in the top-left cell of the resulting range; the cells it spills into are calculated outputs, not independently entered formulas. SORT, FILTER, UNIQUE, and SEQUENCE are the functions built specifically to return arrays, though any formula that produces more than one result will spill.
Why It Matters¶
Before dynamic arrays, a formula that needed to produce a list of results — a sorted list, a filtered subset, a list of unique values — had to be entered once and copied down every row it needed to fill, or built with an array formula entered with Ctrl+Shift+Enter. A dynamic array formula does this with a single formula in a single cell, and resizes its own output automatically as the underlying data changes. See Dynamic Arrays and Power Query in Modelling for how this changes the audit considerations that apply to a spilled range compared with a conventional, row-by-row formula structure.
Continue Reading¶
Prerequisites¶
- Financial Modelling Best Practices — the parent pillar
Related Technical Guides¶
Related Glossary¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What is a dynamic array formula in Excel?
A formula that returns more than one value and automatically places ("spills") those values into the neighbouring cells, without needing to be copied down or across manually.
What functions use dynamic arrays?
SORT, FILTER, UNIQUE, and SEQUENCE are the core dynamic array functions, alongside any formula that returns an array of results, such as a formula referencing a full column or using the spilled range operator.
Can I edit a cell inside a spilled range directly?
No. Only the top-left cell of a spilled range contains the actual formula. The other cells in the spill area display the results of that formula and cannot be edited independently; attempting to do so returns a spill error.
Related Articles
Dynamic Arrays and Power Query in Financial Modelling
Dynamic arrays and Power Query are the two Excel capabilities that have changed most significantly since the structural conventions in the FAST Standard and the ICAEW Financial Modelling Code were first written. Dynamic array functions such as SORT, FILTER, and UNIQUE let a single formula return and automatically resize a range of results, replacing formulas that previously had to be copied down or entered as legacy array formulas. Power Query lets a model ingest and clean external data through a recorded, repeatable transformation sequence rather than a manual copy-paste-and-clean step. Both are genuine productivity gains, and both introduce dependency structures that a conventional row-by-row, cell-by-cell review does not automatically surface — a spilled range is owned by one formula rather than many, and Power Query's transformation steps sit entirely outside the worksheet grid.
Financial Modelling Best Practices — Standards Compared
Financial modelling best practice is not a single document but a landscape of named institutional standards, each publishing its own conventions for how a model should be structured, formatted, and documented. This page defines that landscape — what a named modelling standard actually is, how the FAST Standard and the ICAEW Financial Modelling Code differ in approach and scope, and how a practitioner chooses between them or applies more than one. It sits beside, not instead of, the Knowledge Centre's structural-foundation page on what makes an Excel financial model reliable — this page is about who has codified that discipline into a named standard, and how those standards compare to one another.
Power Query
Power Query is Microsoft's data connectivity and preparation engine, built into Excel and other Microsoft products, used to connect to a data source, apply a recorded sequence of transformation steps, and load the result into the workbook. In financial modelling, Power Query is typically used to ingest and clean external data — an accounting system export, a data room file, a market data feed — before that data reaches the model's calculation layer, replacing what would otherwise be a manual copy-paste-and-clean step performed by hand each time the source data updates.