What Happened to the Calculation Array — and What Replaced It?

If you've opened a spreadsheet recently and noticed that a formula behaves differently than it did a few years ago — or you inherited a workbook and can't figure out why certain formulas return a single value instead of the range you expected — you're likely bumping into one of the most significant shifts in spreadsheet history: the move away from legacy calculation arrays toward dynamic array behavior.

Understanding this change doesn't just solve a mystery. It fundamentally changes how you think about building formulas.

What a Calculation Array Actually Was

In traditional spreadsheet applications — Excel pre-2019, older versions of Google Sheets, LibreOffice Calc — an array formula was a special construct. You'd enter a formula and press Ctrl+Shift+Enter instead of just Enter. The application would wrap the formula in curly braces {=...} to signal that it was processing a range of values simultaneously rather than a single cell.

These were called CSE formulas (Ctrl+Shift+Enter), and they allowed calculations that would otherwise require multiple helper columns or complex workarounds. A single CSE formula could multiply two columns together element by element, sum conditionally across a range, or extract values meeting specific criteria.

The limitation was significant: the array was static in size. You had to manually define how many rows or columns the result would occupy, select that exact range before entering the formula, and if the source data changed size, the formula often broke or silently returned wrong values.

The Shift to Dynamic Arrays 🔄

Starting with Excel 365 and Excel 2019 (with full rollout through Microsoft 365), and progressively in Google Sheets, the underlying calculation engine was rebuilt. The concept of a "spill range" was introduced.

Now, when a formula naturally produces multiple values, it automatically expands to fill adjacent cells — no Ctrl+Shift+Enter required, no pre-selecting a range. The formula just spills.

This behavior is enabled by a new class of functions built around dynamic arrays:

• FILTER() — returns a range of rows meeting a condition
• SORT() / SORTBY() — returns a sorted version of a range
• UNIQUE() — returns distinct values from a list
• SEQUENCE() — generates a numeric array on the fly
• XLOOKUP() — can return entire rows or columns, not just single values

These functions didn't just add convenience. They replaced entire architectures of nested legacy formulas.

Why This Matters for Anyone Using Spreadsheets

The practical impact varies by how you use spreadsheets.

For light users building simple trackers or lists, the shift is largely invisible. Basic SUM, IF, and VLOOKUP formulas still work as they always have.

For intermediate users who relied on CSE formulas for conditional aggregation or multi-column lookups, dynamic arrays remove a significant amount of complexity. A task that once required an array formula wrapped around INDEX/MATCH might now be a single XLOOKUP.

For power users and anyone maintaining legacy workbooks, the gap is more nuanced. Old CSE formulas don't automatically convert. A workbook built in Excel 2010 and opened in Excel 365 will preserve its CSE formulas — but you won't be able to create new ones the same way, and mixing old and new logic in the same file can produce unexpected behavior.

The Version Variable Nobody Talks About Enough

Dynamic array support is not uniform across platforms:

• Microsoft 365 (subscription) — full dynamic array engine, all new functions available
• Excel 2019 (perpetual license) — partial support; some dynamic functions present, behavior varies
• Excel 2016 and earlier — no native dynamic arrays; CSE only
• Google Sheets — has adopted many equivalent functions (FILTER, UNIQUE, SORT) natively, with its own spill-like behavior, though syntax and edge cases differ
• LibreOffice Calc — has array formula support but dynamic spilling behavior lags behind Excel 365

This version gap is why tutorials contradict each other, why a formula copied from a forum post doesn't work on your machine, and why "where once there was a calculation array" — a CSE-based approach — there is now either a dynamic equivalent, a compatibility gap, or a silent error depending entirely on which version you're running. 🖥️

What the Spill Operator Changed

One subtle but important addition is the spill range reference operator (#). If a formula in cell A1 spills results into A1:A10, you can reference the entire dynamic output elsewhere with A1#. This creates a living reference — if the source data grows and A1 now spills into A1:A15, the reference updates automatically.

This is a meaningful architectural shift. Ranges are no longer just fixed addresses. They're potentially formula-driven, variable-length regions — which changes how you design data models, dashboards, and dependent calculations.

The Factors That Determine What You're Working With

Whether a legacy array approach, a dynamic array approach, or some hybrid is appropriate depends on several intersecting variables:

• Which version of your spreadsheet application is installed — and whether it auto-updates
• Who else opens the file — collaborators on older versions may see errors where you see results
• The size and volatility of your data — whether your source ranges grow, shrink, or stay fixed
• Your formula complexity — whether you're doing simple lookups or multi-condition, multi-column extractions
• The platform — Excel desktop, Excel Online, and Google Sheets handle some of these behaviors differently even when using identical function names

Where once there was a calculation array — a deliberately constructed, carefully sized CSE formula — there may now be a cleaner dynamic equivalent. Or there may not be, depending on your environment. That distinction lives entirely in the specifics of your setup. 📊