Excel ARRAYFORMULA: 3 Equivalents That Actually Work

If you're moving a model from Google Sheets to Excel, or building something that has to run on both, knowing which mechanism to reach for matters more than knowing they exist.

Why Excel Has No ARRAYFORMULA (and What Google Sheets Does Differently)

Google Sheets designed ARRAYFORMULA as an explicit wrapper: you put it around a formula, it expands the result down the column automatically, and it recalculates when the range grows. One cell, one formula, 800 rows covered. The formula in C2 is =ARRAYFORMULA(SUMIFS('P&L'!D:D,'P&L'!B:B,A2:A800,'P&L'!C:C,"Recurring")) and nothing else exists in C3:C800.

Excel took a different path. Array behavior was bolted on via keyboard shortcut in legacy versions, then redesigned as a native spill engine in 2020. The result is that Excel actually has more granular control - but the fragmentation across versions creates real compatibility problems in models that need to run on Excel 2016 desktops in a bank syndicate's IT environment.

Three Excel ARRAYFORMULA Alternatives

CSE Arrays (Legacy)

Before Excel 365, the only way to do array math was Ctrl+Shift+Enter. This wraps a formula in curly braces {=...} and evaluates it as an array. The formula {=SUMPRODUCT((Revenue!B2:B500=Assumptions!$B$3)*Revenue!C2:C500)} works back to Excel 97, which is why it's still alive in models built before January 2020.

The fatal flaw: CSE arrays don't auto-resize. You enter the formula for 500 rows, it covers 500 rows. If your headcount grows from 500 to 800, you manually re-enter the formula with the expanded range - or your model silently understates. In a quarterly board pack where someone added 3 new headcount lines last week, that's a credibility problem.

Dynamic Arrays (Excel 365 / 2019+)

Microsoft shipped dynamic arrays with general availability in January 2020 across Microsoft 365. The spill engine means a formula entered in a single cell automatically fills as many cells as the output requires. No Ctrl+Shift+Enter, no fixed-range commitment.

=FILTER('P&L'!C:C, 'P&L'!B:B="Revenue")

That one formula in D2 returns every revenue line from the P&L tab and spills down however many rows it needs. Add a revenue line to the source data and the spill range expands on next calculation. This is the closest behavioral equivalent to Google Sheets ARRAYFORMULA - and the spill range operator (#) lets you reference the full output downstream.

The constraint is hard: dynamic arrays require Microsoft 365 or Excel 2019. Excel 2016 returns #NAME?. If your model goes to a PE fund running Excel 2016 on locked-down laptops, every FILTER, UNIQUE, and SEQUENCE cell breaks on open.

SUMPRODUCT (Universal)

SUMPRODUCT has been in Excel since Excel 2007 and handles array math without special entry - just normal Enter. It's the closest thing to a version-safe array formula.

=SUMPRODUCT(
  ('P&L'!$B$2:$B$500>=Assumptions!$B$3)*
  ('P&L'!$B$2:$B$500<=Assumptions!$B$4)*
  ('P&L'!$C$2:$C$500="Recurring"),
  'P&L'!$D$2:$D$500
)

That covers the date-range, category-filter, revenue-sum pattern that shows up constantly in FP&A models. Works in every Excel version since 2007. The trade-off: SUMPRODUCT returns a single value. You can't use it to spill an array down a column the way ARRAYFORMULA or dynamic arrays do.

How to Choose an Excel ARRAYFORMULA Replacement

The version question is usually decisive. If you control the environment (internal model, Microsoft 365 tenant), use dynamic arrays - they're cleaner, auto-resize, and support the full FILTER/SORT/UNIQUE toolkit. If the model leaves your hands (bank package, LP reporting, board deck with external directors), SUMPRODUCT is the safe default for aggregation and CSE arrays are acceptable for multi-output scenarios where you can guarantee a fixed range.

One non-obvious edge case worth knowing: CSE arrays and dynamic arrays don't mix well in the same workbook. Microsoft's documentation notes that entering a dynamic array formula in a range that overlaps with a CSE array throws a #SPILL! error. If you're upgrading a legacy model, convert the CSE arrays first or isolate the two approaches on separate sheets.

What This Means for Multi-Tab FP&A Models

The version fragmentation is manageable if you build it into your architecture. A practical approach: keep aggregation formulas (SUMIFS, SUMPRODUCT, cross-tab lookups) version-safe, and isolate dynamic array features (FILTER, UNIQUE, SEQUENCE) to a dedicated "Calculations" tab that you clearly flag as requiring Microsoft 365.

For a three-statement model where the assumptions tab feeds the P&L, which feeds the balance sheet and cash flow, the critical formulas are the aggregators. Those should be SUMPRODUCT or standard SUMIFS - not FILTER or UNIQUE - because they need to survive distribution. The dynamic array features live in the analysis layers (returns analysis, sensitivity tables, contribution margin by SKU) where you control who opens the file.

// SUMPRODUCT version-safe approach for distribution:
=SUMPRODUCT(
  ('Cash Flow'!$B$2:$B$120=Assumptions!$B$5)*
  ('Cash Flow'!$C$2:$C$120="Operating"),
  'Cash Flow'!$D$2:$D$120
)

// Dynamic array approach for internal 365 environment:
=SUM(FILTER('Cash Flow'!D:D,
  ('Cash Flow'!B:B=Assumptions!$B$5)*
  ('Cash Flow'!C:C="Operating")))

Both return the same number. Only one of them opens without errors on a 2016 machine.

ModelMonkey handles the version detection problem automatically - when writing formulas against your workbook, it checks your Excel version and picks the right array mechanism. That matters when you're building a model that needs to run in both your Microsoft 365 environment and a client's older install. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.

As of July 2026, the version split is still real. Microsoft 365 penetration in enterprise is high but not universal, and older perpetual licenses stay in production far longer than IT roadmaps suggest.