ARRAYFORMULA vs Excel Dynamic Arrays
Both let one formula fill many cells. Both handle ranges as arrays. But Google Sheets' ARRAYFORMULA is an explicit wrapper you type; Excel's Dynamic Arrays are automatic spilling that happens without any keyword. The difference matters more than it sounds — it shapes how you think about, write, and migrate formulas across the two platforms.
⚡ Quick Answer
ARRAYFORMULA (Google Sheets) is a wrapper: =ARRAYFORMULA(A2:A100*B2:B100). You explicitly activate array behavior. Dynamic Arrays (Excel 2019+/Microsoft 365) are implicit: =A2:A100*B2:B100 spills automatically. Same result, different syntax philosophy. Modern Sheets functions like FILTER and SORT already return arrays natively, so you often don't need the wrapper.
📋 What's in this comparison
The core concept — why array formulas exist
In old-school spreadsheets, one formula returned one value. To compute totals for 100 rows, you wrote the formula in row 1 and dragged it down 100 times. Simple, but repetitive — and every cell was a separate formula the sheet had to track.
Array formulas break that model. One formula fills a range. Change the source data and the whole range updates. Fewer formulas to maintain, more consistent results, less risk of some rows being out of sync because someone forgot to drag.
Both Sheets and Excel offer this, but with different mental models. Sheets treats array behavior as opt-in; Excel (in modern versions) treats it as default.
ARRAYFORMULA in Google Sheets
ARRAYFORMULA is a wrapper function. Without it, most functions return one value even if you give them ranges. With it, the function operates on every element of the input range and returns a matching output range.
=ARRAYFORMULA(A2:A100 * B2:B100)
This multiplies A2*B2, A3*B3, ..., A100*B100 in a single formula. The result fills 99 cells starting from wherever you typed the formula.
Without ARRAYFORMULA
Some Sheets functions are already "array-aware" and don't need the wrapper:
FILTER,SORT,UNIQUE,QUERY,SEQUENCE,SPLIT,IMPORTRANGEXLOOKUP(when returning an array)- Any function specifically designed to return a range
These spill by default — no wrapper required. But most functions (IF, VLOOKUP, arithmetic, text functions) need ARRAYFORMULA to spill.
When you need ARRAYFORMULA
=ARRAYFORMULA(IF(A2:A100 > 100, "High", "Low"))
=ARRAYFORMULA(VLOOKUP(A2:A100, table, 2, FALSE))
=ARRAYFORMULA(LEFT(A2:A100, 3))
=ARRAYFORMULA(A2:A100 & " - " & B2:B100)
Rule of thumb: if your formula uses a range and you want it to spill, wrap with ARRAYFORMULA. If it uses a range but you only want one output cell, no wrapper needed.
Dynamic Arrays in Excel
Excel 2019 and Microsoft 365 introduced Dynamic Arrays — a rebuild of how Excel's calc engine handles formula results. Any formula that returns multiple values automatically "spills" into adjacent cells.
=A2:A100 * B2:B100
Type this in a single cell, press Enter, and Excel fills 99 cells with the products. No keyword, no wrapper. If the destination cells are already occupied, Excel shows #SPILL! so you know to clear the range.
New Dynamic Array functions
Modern Excel also added functions designed specifically for spilling:
FILTER,SORT,SORTBY,UNIQUE,SEQUENCE,RANDARRAYXLOOKUP,XMATCHTEXTSPLIT,TEXTJOIN,TOCOL,TOROW,WRAPCOLS,WRAPROWS
These all leverage the spill engine. Google Sheets has native equivalents for most, though a few (like SEQUENCE) took longer to appear in Sheets.
Legacy array formulas (CSE)
Before Dynamic Arrays, Excel used "CSE" — array formulas activated with Ctrl+Shift+Enter. These still work for backward compatibility but shouldn't be used in new work. Modern Excel handles them automatically.
Side-by-side comparison
| Aspect | ARRAYFORMULA (Sheets) | Dynamic Arrays (Excel) |
|---|---|---|
| Syntax | Explicit wrapper | Implicit / automatic |
| Activation | Type ARRAYFORMULA(...) | Just type the formula |
| Available since | Google Sheets launch | Excel 2019 (public), M365 |
| Older Excel behavior | N/A | CSE with Ctrl+Shift+Enter |
| Spill error indicator | Overwrites blocking cells (no error) | #SPILL! when blocked |
| Native array functions | FILTER, SORT, UNIQUE, QUERY, etc. | FILTER, SORT, UNIQUE, SEQUENCE, etc. |
| Non-array functions | Wrap to array-ify | Most already spill natively |
| Referencing spill range | Point to range directly | Use # notation: A2# |
| Learning curve | Steeper — must learn wrapper | Gentler — just works |
| Discoverability | You know when it's an array formula | Sometimes surprises new users |
Migration between platforms
Sheets → Excel: remove the wrapper
Google Sheets
=ARRAYFORMULA(
A2:A100 * B2:B100)
Excel 2019+
=A2:A100 * B2:B100
In most cases, dropping the ARRAYFORMULA( and matching ) is all you need. Excel's automatic spilling handles the array behavior.
Excel → Sheets: add the wrapper where needed
Excel 2019+
=IF(A2:A100 > 100,
"High", "Low")
Google Sheets
=ARRAYFORMULA(
IF(A2:A100 > 100,
"High", "Low"))
Sheets' IF isn't array-aware by default — it needs ARRAYFORMULA to spill. Same story for VLOOKUP, arithmetic, text functions.
Both platforms: modern array functions work identically
Both Sheets AND Excel
=FILTER(A2:C100,
B2:B100 = "North")
Both Sheets AND Excel
=SORT(
FILTER(A2:C100,
B2:B100 = "North"),
3, FALSE)
FILTER, SORT, UNIQUE, and XLOOKUP work the same syntax in both platforms. No wrapper needed either way.
Gotchas each way
Sheets gotchas
Forgetting the wrapper. =IF(A2:A100 > 100, "High", "Low") without ARRAYFORMULA just returns "High" or "Low" for the FIRST row — no spill. Silent bug. Wrap it.
Overwriting existing data. Sheets doesn't warn you if ARRAYFORMULA output would overwrite cells with data. It just does. Make sure the destination range is empty.
Text concatenation is verbose. Excel's & in a Dynamic Array spills easily. Sheets requires ARRAYFORMULA: =ARRAYFORMULA(A2:A100 & " - " & B2:B100).
Excel gotchas
#SPILL! errors when destination is blocked. Excel refuses to overwrite existing data. Move or delete whatever's in the way, or shrink your input range.
Implicit intersection with @ operator. In older Excel formulas, ranges were sometimes coerced to single values by "implicit intersection." Modern Excel uses the @ operator to make this explicit. If you paste an old formula and see @ characters, that's Excel preserving legacy behavior.
Spill range references. To reference the FULL spill range dynamically, use A2# (the hash means "the whole spill starting at A2"). Sheets doesn't have this — you reference a fixed range or use the ARRAYFORMULA output directly.
If you frequently switch between Sheets and Excel, favor the modern array functions (FILTER, SORT, UNIQUE, XLOOKUP) that behave identically in both. Avoid ARRAYFORMULA-wrapping arithmetic in Sheets when you can achieve the same result with SUMPRODUCT or QUERY — those port more predictably to Excel.
The verdict
🎯 Bottom line
Neither is "better" — they solve the same problem with opposite defaults. Sheets' explicit approach makes array behavior obvious in the formula text. Excel's implicit approach reduces the syntactic overhead but sometimes surprises users when a formula spills unexpectedly.
For cross-platform work, rely on the modern array functions. FILTER, SORT, UNIQUE, and XLOOKUP work identically in both. When you need array behavior beyond those, ARRAYFORMULA in Sheets and plain formulas in Excel are the workarounds.
Learn the mental model for both platforms if you use both. The syntax differs; the concept is the same. Once you internalize "one formula, many outputs," moving between the two is straightforward.
Frequently asked questions
Are ARRAYFORMULA and Dynamic Arrays the same thing?
Not exactly. Both let a single formula produce multiple output cells, but the mechanisms differ. ARRAYFORMULA is an explicit wrapper — you write =ARRAYFORMULA(A2:A100*B2:B100) to activate array behavior. Dynamic Arrays in Excel 2019+/Microsoft 365 are automatic — most functions spill into multiple cells natively without any wrapper. The result is similar; the syntax and default behavior differ significantly.
Does Excel have an equivalent to ARRAYFORMULA?
Legacy Excel had array formulas activated with Ctrl+Shift+Enter (CSE). Modern Excel (2019+/Microsoft 365) replaced this with Dynamic Arrays that spill automatically. Neither uses the ARRAYFORMULA keyword. If you're migrating a formula from Sheets to Excel, remove the ARRAYFORMULA wrapper and the formula typically works — Excel spills natively.
Do Dynamic Array functions like SORT and FILTER work in Google Sheets?
Yes, all of them. Google Sheets has native versions of SORT, FILTER, UNIQUE, SEQUENCE, XLOOKUP, and XMATCH. Sheets was ahead on some of these — SORT and FILTER existed for years before Excel added them. Cross-platform users can rely on all major Dynamic Array functions working in both Sheets and modern Excel.
When do I need ARRAYFORMULA in Google Sheets?
Wrap with ARRAYFORMULA when you want a function that ISN'T natively array-aware to output multiple rows. Examples: =ARRAYFORMULA(IF(A2:A100>0, "Yes", "No")) makes IF return an array. Modern Sheets functions like FILTER, SORT, UNIQUE, and QUERY already return arrays — no wrapper needed. Rule of thumb: if your formula only fills one cell but you want it to fill a range, ARRAYFORMULA is the fix.
Which is easier to learn — ARRAYFORMULA or Dynamic Arrays?
Dynamic Arrays are easier for beginners because they just work — no wrapper, no special syntax. Modern Excel behaves as beginners expect: type a formula, get multiple results if the inputs are ranges. ARRAYFORMULA is more explicit — you know exactly when you're activating array behavior, which is clearer for understanding what a formula does, but requires learning the wrapper.