Excel vs Google Sheets: the formula compatibility guide

Most Excel-vs-Sheets comparisons argue about collaboration and price. This one covers the part that silently breaks when you switch: the formulas — which functions exist on each side, where the same function behaves differently, and how to keep a workbook portable.

The numbers

• 468 functions are documented for BOTH Excel and Google Sheets — the shared core where most everyday work lives.
• 54 functions are Excel-only (full list): PIVOTBY, GROUPBY, the CUBE family, AGGREGATE… Importing an .xlsx that uses them leaves #NAME? cells in Sheets.
• 47 functions are Sheets-only (full list): QUERY, ARRAYFORMULA, IMPORTRANGE, GOOGLEFINANCE, the REGEX family… These die on export to Excel.

Same function, different dialect

The subtler traps are functions both apps HAVE but spell differently:

• Array formulas. Sheets wraps per-row math in ARRAYFORMULA(...); modern Excel just spills array expressions natively. Exporting a Sheet frequently degrades wrapped arithmetic to a single-cell result — details in ARRAYFORMULA vs dynamic arrays.
• Filtering with SQL. Sheets’ QUERY has no Excel equivalent at all; the portable subset is FILTER/SORT/UNIQUE — see FILTER vs QUERY.
• Regular expressions. REGEXMATCH/REGEXEXTRACT/REGEXREPLACE are Sheets-only; Excel’s closest tools are SEARCH wildcards and TEXTBEFORE/TEXTAFTER.
• Version skew inside Excel itself. XLOOKUP, FILTER, LAMBDA and friends need Excel 2021+/365 — a current Sheet can be MORE compatible with modern functions than a 2019 Excel install.

Keeping a workbook portable

1. Stick to the shared core — paste any formula into the compatibility checker and it flags every function per app.
2. Prefer FILTER/SORT/UNIQUE compositions over QUERY, and native ranges over ARRAYFORMULA, when a file might ever leave Sheets.
3. Avoid the newest Excel exclusives (GROUPBY, PIVOTBY) in files that co-workers will open in Sheets.
4. Test the round trip: export, reopen, and search for #NAME? and #REF!.

Where LibreOffice fits

LibreOffice Calc reads both formats and — unlike either vendor’s docs — we can actually EXECUTE formulas in it: 215 functions live-tested across 24.2.0.3, 24.8.7.2, 25.2.0.3, 25.8.7.3. Its modern-function support now tracks Excel closely (XLOOKUP since 24.8, the dynamic-array batch since 25.8 — see support by version), and several “Excel-only” functions like AGGREGATE work fine there, making it a viable escape hatch for files Sheets can’t fully open.

Every claim here is backed by the underlying pages: per-function verdicts, behavioral quirks, and the verification methodology.