Dynamic Arrays and Power Query in Financial Modelling
Executive Summary
Key Takeaways
- ✓ Dynamic array functions (SORT, FILTER, UNIQUE, SEQUENCE) let a single formula return and automatically resize a range of results, replacing formulas previously copied down cell by cell or entered as legacy array formulas.
- ✓ A spilled range is owned entirely by the formula in its top-left cell — the cells it spills into are not independently editable, which changes what "the formula in this cell" means for a reviewer.
- ✓ Power Query moves data ingestion and cleaning into a recorded, repeatable transformation sequence that sits outside the worksheet grid, rather than a manual step performed once and then forgotten.
- ✓ Both features change what "hardcoded" and "broken link" mean structurally, because the dependency they introduce is not visible through conventional formula-bar inspection alone.
- ✓ Neither feature is prohibited or discouraged by the FAST Standard or the ICAEW Financial Modelling Code; both standards were written before these features existed and neither currently states a specific position on them.
Institutional Definition¶
Dynamic arrays and Power Query are the two Excel capabilities that most changed how a financial model's calculation and data-ingestion layers can be built since the FAST Standard and the ICAEW Financial Modelling Code were first written. Dynamic array functions — SORT, FILTER, UNIQUE, and SEQUENCE — let a single formula return more than one result, which Excel automatically places into the neighbouring cells. Power Query is Microsoft's built-in data connectivity and transformation engine, used to import and clean external data through a recorded, repeatable sequence of steps rather than a manual, one-time edit. This guide covers what each feature does in a modelling context and, specifically, the new audit considerations they introduce that a conventional, cell-by-cell review does not automatically surface.
Why It Matters¶
Both features are genuine productivity gains over the conventions they replace. Before dynamic arrays, producing a sorted or filtered list required either a manual sort-and-copy step repeated every time the source data changed, or a legacy Ctrl+Shift+Enter array formula that most reviewers found difficult to inspect. Before Power Query, cleaning an external data extract meant a manual sequence of find-and-replace, column splits, and filters performed by hand, with no record of what had been done if the source file needed to be refreshed later.
Both features also change the shape of a model's dependency structure in ways that predate them do not. A spilled range is generated by a single formula rather than by many individually entered ones. A Power Query-loaded table's values depend on a transformation sequence that lives entirely outside the worksheet grid. Neither of these dependency structures is visible to a reviewer who inspects only what appears in a cell's formula bar — which is precisely the review technique that conventional financial model auditing has relied on for cell-by-cell formulas since before these features existed.
Dynamic Arrays in Modelling¶
What Changed¶
A dynamic array formula returns an array of results from a single formula cell, and Excel automatically resizes the output range ("spills") as the underlying data changes. SORT produces a sorted list, FILTER produces a filtered subset, UNIQUE produces a de-duplicated list, and SEQUENCE generates a series of numbers — all from one formula, without needing to be copied down a column or across a row.
This is a genuine structural change from the row-by-row formula convention both the FAST Standard and the ICAEW Code were written around: FAST's formula-per-row consistency rule and the ICAEW Code's equivalent integrity principle both assume that a calculation row's logic is repeated, structurally identically, across every period column. A spilled dynamic array formula does not repeat: it exists once, in one cell, and everything else in its spill range is a calculated output of that single formula rather than a separately entered, individually inspectable formula.
Practical Modelling Uses¶
Dynamic arrays are most useful in a financial model for tasks that were previously either manual or required a legacy array formula: producing a ranked or sorted list of scenario outputs for a summary dashboard, filtering a transaction-level dataset down to a specific period or category before it feeds a calculation, and generating a de-duplicated list of categories or counterparties from a raw data extract.
They are less suited to a model's core period-by-period calculation schedule (revenue, cost, debt service), where the FAST/ICAEW convention of one formula per row, consistently structured across every column, remains the clearer and more auditable pattern — a spilled formula that quietly replaces twenty individually inspectable period cells with one formula a reviewer has to trust in full is not automatically an improvement for a model's core calculation logic, even though it is a clear improvement for the list- and lookup-style tasks described above.
Reviewing a Spilled Range¶
A reviewer examining a spilled dynamic array formula should locate the top-left cell — the only cell that actually contains the formula — and inspect that formula directly, in the same way any other formula cell is inspected. The cells the formula spills into can be identified visually (Excel outlines the spill area with a light border when the top-left cell is selected) but do not themselves contain independently reviewable formulas; reviewing only the spilled output cells without locating the source formula is equivalent to reviewing a formula's result without ever opening its formula bar.
Power Query in Modelling¶
What Changed¶
Power Query connects to a data source, applies a sequence of transformation steps recorded in the Power Query M formula language, and loads the result into the workbook, either as a table of static values or as a connection that can be refreshed against the current state of the source data. In a financial model, Power Query typically sits ahead of the calculation layer: it ingests and cleans an accounting system export, a data room file, or a market data feed before that data reaches any formula.
This introduces a dependency layer that did not exist in the same form when the FAST Standard and the ICAEW Code were written: the model's inputs are no longer produced solely by values typed into designated input cells or formulas visible in the worksheet grid, but partly by a transformation sequence that exists in a separate editor, outside the worksheet entirely.
Practical Modelling Uses¶
Power Query is most valuable in a financial model wherever external data needs to be cleaned or reshaped before it can be used: importing and standardising a general ledger export with inconsistent formatting, combining multiple period files into a single consolidated table, or filtering a large transaction dataset down to the subset relevant to the model before that subset is loaded into the workbook.
Reviewing a Power Query-Dependent Model¶
A model that relies materially on Power Query cannot be fully reviewed by inspecting worksheet formulas alone, because the query's transformation logic is not represented in any cell's formula bar. Reviewing a Power Query-dependent model requires opening the Power Query editor (Data → Queries & Connections, then editing the relevant query) and stepping through the recorded transformation sequence to confirm each step does what the model's documentation claims it does — the same substantive scrutiny applied to a worksheet formula, applied to a different location.
Audit Considerations¶
Both features change what two of the most common structural findings in a financial model audit — hardcoded values and broken links — actually look like, because the mechanism producing the risk has moved outside the location a conventional review checks first.
Hardcoding, in a Power Query context. A Power Query transformation step can embed a fixed value — a filter threshold, a currency conversion rate, a category mapping — directly within a query step, in exactly the same structural sense that a formula cell can embed a hardcoded value. The risk is the same as hardcoded formulas describes: the value will not respond if the model's stated assumptions change, and it is invisible to a reviewer who checks only cell formulas, because it does not live in a cell at all.
Broken links, in a spilled-range context. A formula elsewhere in the model that references a spilled dynamic array range (using the spilled range operator) depends on that entire range continuing to spill successfully. If the source data referenced by the dynamic array formula changes such that the spill can no longer complete — most commonly because the cells the array would spill into are now occupied by something else — Excel returns a #SPILL! error, and every formula referencing that spilled range is affected simultaneously. This is a variant of the broken links risk category: a single point of failure that, when it fails, propagates to every dependent formula at once, in a way a conventional cell-by-cell broken-link check does not anticipate.
Broken links, in a Power Query context. A query connected to an external data source is functionally equivalent to the external broken link risk described in Broken Links: if the source file is moved, renamed, or made unavailable, the query will fail to refresh. Depending on how the query is configured, a failed refresh may leave the previously loaded values in place with no visible indication that they are now stale — the same silent-stale-value risk that makes external workbook links a persistent audit finding, applied to a query connection instead of a cell reference.
Detection is not automatic for either mechanism through conventional cell-formula inspection. A structural review that examines only cell formulas will not detect a hardcoded value embedded in a Power Query step, and will only detect a spill failure if it happens to occur at the time of review rather than intermittently as source data changes. Both should be treated as distinct dependency categories requiring their own specific review step, documented separately from the model's conventional formula audit.
Common Mistakes¶
| Common Mistake | Why It Matters |
|---|---|
| Reviewing only the spilled output cells, not the source formula | The output cells are calculated results, not independently reviewable formulas. The single formula in the top-left cell is what actually needs review. |
| Assuming Power Query transformations are outside the scope of a model review | A materially data-dependent model's inputs are partly determined by query logic that lives outside the worksheet grid, and skipping it leaves a significant portion of the model's input pipeline unreviewed. |
| Embedding fixed thresholds or conversion values directly in a query step | This is structurally the same risk as a hardcoded formula cell — the value will not respond to a change in the model's stated assumptions and is invisible outside the query editor. |
| Treating a #SPILL! error as a simple formula bug rather than investigating its source | A spill failure often indicates that the underlying source range changed in a way that also affects other parts of the model, not only the single formula displaying the error. |
| Not documenting which queries exist, what they source, and how often they refresh | Without this documentation, a reviewer inheriting the model has no starting point for locating and assessing the query logic that produced the model's inputs. |
Best Practices¶
| Best Practice | Why It Matters |
|---|---|
| Keep the model's core period-by-period calculation schedule in conventional, row-consistent formulas | This preserves the formula-per-row transparency both the FAST Standard and the ICAEW Code are built around, reserving dynamic arrays for list, lookup, and summary tasks where their properties are a genuine improvement. |
| Document every Power Query query's source, purpose, and refresh frequency | Because query logic is invisible outside its own editor, this documentation is the only way a reviewer who has not built the model can locate and assess it without a guided walkthrough. |
| Review Power Query transformation steps with the same scrutiny applied to worksheet formulas | A query step that embeds a fixed threshold or filter value carries the same risk as a hardcoded formula cell, and should be reviewed to the same standard. |
| Confirm what a spilled formula's source range is before accepting a downstream formula that references it | Since a single source-range change can affect every formula referencing a spilled range simultaneously, tracing the source is more consequential than for a conventional single-cell reference. |
References & Further Reading¶
The following sources have been verified against their primary publisher and are listed in full, with links, in the References section below. - Dynamic array formulas and spilled array behavior — Microsoft Support - What Is Power Query? — Power Query, Microsoft Learn
Continue Reading¶
Prerequisites¶
- Financial Modelling Best Practices — the parent pillar
Related Technical Guides¶
- Hardcoded Formulas in Financial Models — the structural risk category this guide's Audit Considerations section extends to Power Query
- Broken Links in Financial Models — the structural risk category this guide's Audit Considerations section extends to spilled ranges and query connections
- Excel Performance and Large Model Optimisation
Related Glossary¶
Related Products¶
- Financial Model Audit Engine (FMAE) — deterministic structural auditing of a model's formulas; FMAE's structural rules do not currently include dynamic-array- or Power-Query-specific detection logic
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
Do dynamic arrays and Power Query violate the FAST Standard or the ICAEW Code?
Neither standard currently states a specific position on dynamic arrays or Power Query, since both predate these Excel features. Using them does not violate either standard's stated principles; a modelling team applying either standard should document how it has interpreted the standard's existing conventions (colour coding, formula-per-row consistency) in the context of a spilled range or a query-loaded table.
Can I reference a cell inside a spilled range in a formula?
Yes, but only by referencing the spilled range as a whole (using the spilled range operator, a trailing hash symbol applied to the top-left cell) or by referencing the calculated value at a specific position within it. You cannot type a formula directly into an individual cell inside an active spill area, since that cell is a calculated output of the top-left formula, not an independently editable cell.
What happens if a formula referencing a spilled range is broken by a change elsewhere in the model?
If the source data referenced by a dynamic array formula changes such that the formula can no longer spill into the space available (for example, because the cells it would spill into are occupied), Excel returns a SPILL error rather than partially calculating. This is more visible than some broken-link failure modes, but the underlying cause can still require investigation of the source range and any formulas that changed its size.
Does a Power Query transformation show up when I audit a workbook's formulas?
Not in the same way a cell formula does. A loaded query's output appears in the worksheet as a table of values (or as the source for further formulas), but the transformation logic that produced those values lives in the Power Query editor, not in any cell's formula bar. A reviewer who inspects only cell formulas will not see the transformation steps that produced the data those formulas depend on.
Should Power Query queries be documented the same way named ranges or key assumptions are?
Yes. Because a query's transformation steps are not visible through normal worksheet navigation, a model that relies materially on Power Query benefits from documenting, at minimum, each query's data source, its purpose, and its refresh frequency — the same documentation discipline applied to any other structural dependency in the model.
References
Related Articles
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.
Dynamic Arrays
A dynamic array formula is a single Excel formula that returns more than one result, which Excel automatically places ("spills") into the neighbouring cells below and to the right of the formula cell, without the formula needing to be copied down or across manually. Functions such as SORT, FILTER, UNIQUE, and SEQUENCE are dynamic array functions. The spill range is generated and owned by a single formula in the top-left cell; the cells it spills into are not independently editable.
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.
Hardcoded Formulas in Financial Models
A hardcoded value in a financial model is a fixed numeric value embedded directly within a formula cell, rather than being referenced from a dedicated input or assumption cell. Hardcoded values in formula cells are a structural risk because they do not update when the model's assumptions change, they are invisible during normal model navigation, and they cannot be changed consistently through the model's standard input interface. The ICAEW Financial Modelling Code and the FAST Standard both explicitly prohibit hardcoded values within formulas, requiring that all input values be entered in a dedicated input cell and referenced by formulas rather than embedded within them.
Broken Links in Financial Models
Broken links in financial models are references to cells, ranges, or external files that no longer resolve correctly. They occur in two forms: internal broken links, where a cell references a named range, cell address, or worksheet that has been deleted or renamed within the same workbook; and external broken links, where a cell references a cell or range in a separate workbook file that is unavailable, moved, or renamed. Both types can cause formula cells to return errors or silently retain stale cached values, distorting model outputs without visible indication to the user.