ARRAYFORMULA vs dynamic arrays: the same idea, three dialects
Google Sheets makes formulas operate over whole ranges by wrapping them in ARRAYFORMULA; modern Excel and LibreOffice instead auto-spill any array expression. Same concept, incompatible spellings — and the number-one thing that silently breaks when a Sheet is exported to .xlsx.
The differences at a glance
| FILTER | SEQUENCE | UNIQUE | |
|---|---|---|---|
| Per-row math over a range | Sheets: =ARRAYFORMULA(A2:A10*B2:B10) | Excel 365 / LO 24.8+: =A2:A10*B2:B10 just spills | — |
| ARRAYFORMULA function itself | Sheets-only — #NAME? everywhere else | Not needed; arrays are native | — |
| Dynamic-array functions (FILTER/UNIQUE/SEQUENCE...) | Available, no wrapper needed | Available (LibreOffice since 24.8 — executed result) | — |
| Spill collision behavior | #REF! mentioning the blocking cell | #SPILL! (Excel) / err:522-style (LO) | — |
| Old-version fallback | ARRAYFORMULA works in all Sheets | Ctrl+Shift+Enter legacy array formulas | — |
Which should you use?
- ARRAYFORMULA (Sheets) — Sheets-only workbooks. If the file might ever export to Excel/LibreOffice, prefer the dynamic-array functions (FILTER, UNIQUE, SEQUENCE...) that exist on both sides, and avoid wrapping plain arithmetic.
- Native spilling (Excel/LO) — Excel 365/2021 and LibreOffice 24.8+ — write the array expression directly. For files that must open in genuinely old versions, neither dialect survives; stick to fill-down formulas.
Compatibility (from executed tests)
From our executed tests: the shared dynamic-array functions (FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP...) work in LibreOffice 24.8+ and are documented in both Excel 365 and Sheets — they are the portable core. ARRAYFORMULA itself exists only in Google Sheets; on export, Sheets rewrites what it can, but wrapped arithmetic frequently degrades to a single-cell result.
Example formulas
| Sheets dialect | =ARRAYFORMULA(A2:A10*B2:B10) |
| Excel/LibreOffice dialect (auto-spill) | =A2:A10*B2:B10 |
| Portable across all three (modern versions) | =FILTER(A2:A10,B2:B10>100) |
Full per-version details on each function page: FILTER · SEQUENCE · UNIQUE.