Established onExcel 365 and Excel 2021: availableExcel 2019 and earlier: name error on openGoogle Sheets, web: ARRAYFORMULA, different syntax
In current Excel a formula that produces several values writes them into the cells below or beside it, and the block that appears is owned by the single formula that produced it. That is a change to how the calculation engine works, not a new function, and it divides the product into a before and an after.
What it replaced
Formulas returning multiple values existed for decades and had to be entered by selecting the destination range first and confirming with a three-key combination. The result was stored surrounded by braces, could not be edited one cell at a time, and had to be re-entered entirely if the shape of the data changed.
Almost nobody used them, and the ones in existing files were usually written by one person in the organisation.
What arrives with it
A group of functions that only make sense in this model: filtering a range to the rows meeting a condition, sorting, returning distinct values, generating a sequence. Each replaces an arrangement that previously needed either a helper column, a sorted copy, or one of the old multiple-value formulas.
The practical effect is that a whole category of intermediate scaffolding disappears from sheets written after the change.
The compatibility mechanism, which confuses people
Old formulas had to keep behaving as before. So an operator was introduced meaning take the single value that applies to this row, and existing formulas acquired it automatically when opened in the new engine. Seeing that symbol appear in a formula nobody edited is alarming and is the engine preserving the old meaning.
The blocking error
If anything occupies a cell the result needs, nothing is written and the formula reports that it cannot expand. This is a good error: it is visible, it names the cause, and it prevents the alternative, which would be overwriting somebody's data.
It also means the layout of a sheet now constrains its formulas in a way it did not before, and inserting a column into a block that a formula produces is not possible at all.
Where this is and is not available
Present in Excel 365 and Excel 2021. Absent from Excel 2019 and everything earlier, where a file using it shows a name error, and the formulas are stored internally with a prefix marking them as belonging to a later version.
Google Sheets reached the same place by another route and earlier, with a wrapper function that makes a formula operate over a range. The syntax is different, the model is different in detail, and formulas do not transfer between the two products without rewriting.
This is the sharpest version-dependency in the whole subject, which is why this site labels every claim. A guide that says to use one of these functions, without saying which products and versions have it, is a guide that fails for a substantial share of its readers on the first step.
What to do in a mixed organisation
Establish what the oldest version anybody will open the file with is, before writing anything, and treat that as a constraint on the whole workbook. Retrofitting is expensive: replacing these formulas means reintroducing the scaffolding they removed.
Where the file is shared outside the organisation, assume the older version, because you cannot know.
What we cannot verify
Availability by version and the behaviour of the compatibility operator are documented by the vendor and were confirmed on a current build. The behaviour when opening such a file in Excel 2019 is taken from the vendor's documentation rather than from testing, as we do not have that version. Google Sheets behaviour was established by testing in August 2026 and the products change.
What it changes about how sheets are built
The older style spread a calculation across many cells, each holding a small formula, and the arrangement of the cells was part of the logic. The newer style concentrates it: one formula produces a whole result, and the sheet has fewer, larger, more readable pieces.
That is better for review and worse for tracing, because a single cell now contains several steps and the intermediate values no longer exist anywhere to be inspected. The trade appears again in the entry on tracing in the final part.
How to tell which engine a file was written for
Open it and look for the compatibility operator in formulas nobody has edited. Its presence means the file predates the change and has been opened in the newer engine, which is useful to know before assuming any of the newer functions are safe to add.
The performance side
One formula producing a thousand results is generally faster than a thousand formulas producing one each, because the engine evaluates it once. In sheets that had become slow, replacing filled-down columns with single expanding formulas is often the largest available improvement.
The exception is when the expanding formula depends on volatile functions, which the next entry but one is about.
In short
- A formula producing several values writes them into neighbouring cells and owns the block.
- The old way required selecting the range first and a three-key confirmation.
- Filtering, sorting and distinct-value functions arrive with it and remove scaffolding.
- Old formulas gained an operator preserving their previous single-value meaning.
- Anything in the way produces a visible error rather than overwriting data.
- Absent from Excel 2019 and earlier, and Sheets reached the same place with different syntax.