Differences Between Sheets and Excel Formulas to Know

A spreadsheet is a spreadsheet, until a workbook leaves one product for the other. The totals match, the lookups work, and then one tab full of #NAME? appears because a function that Sheets has had for years does not exist in the version of Excel on the recipient's desk. The common core of the two is large, and the differences cluster in predictable places. This article lists them, explains the mechanisms behind the ones that cause trouble and offers a way to keep a file portable. It is useful when you work from Google Sheets alongside databases and when you convert files with converting Google Sheets to CSV or Excel with a script.

The shared core

The functions most workbooks rely on are the same: SUM, AVERAGE, COUNT, COUNTA, IF, IFS, SUMIF, SUMIFS, COUNTIF, VLOOKUP, HLOOKUP, INDEX, MATCH, LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE, TEXTJOIN, DATE, EDATE, EOMONTH, ROUND and many more. Their arguments and results agree in everyday use. This is why simple files convert cleanly, and why teams assume the whole language matches.

Both also now offer XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, LET and LAMBDA, though they arrived at different times, and older Excel versions (2016, 2019) lack many. Check what your recipients actually run.

Arrays: ARRAYFORMULA versus spill

Both products can return a range of results from one formula, and they arrived at it by different routes.

In Sheets, a formula normally works on one cell's worth of input. To apply it down a column you wrap it in ARRAYFORMULA:

=ARRAYFORMULA(IF(A2:A100 = "", "", B2:B100 * C2:C100))

Without the wrapper, the formula calculates only for the first row. Some functions, such as FILTER, SORT, UNIQUE and QUERY, return arrays on their own.

In current Excel (365 and 2021), any formula that returns an array spills automatically into the cells below and to the right, with no wrapper: =B2:B100 * C2:C100 just works, and the result shows a spill range. Older Excel needed an array entered with Ctrl+Shift+Enter, stored as a legacy array formula in braces. A workbook produced by a script with the older convention behaves differently in a new Excel, and a spilled formula written with a library that does not know about spills may arrive with an implicit intersection operator (@) that changes the result (array formulas and why they break imports covers the file-level effects).

When a file moves, formulas using ARRAYFORMULA often arrive in Excel as an unsupported function, and the other direction can lose a spill or insert @.

Functions that exist in only one

Some of the most useful functions have no counterpart.

Sheets only (at the time of writing):

• QUERY, the SQL-like language over a range (QUERY function in Sheets versus SQL).

• IMPORTRANGE, IMPORTDATA, IMPORTHTML, IMPORTXML, IMPORTFEED, which fetch data live.

• REGEXMATCH, REGEXEXTRACT, REGEXREPLACE, giving regular expressions inside formulas.

• SPLIT, which splits text across cells (Excel has TEXTSPLIT in newer versions, with different arguments).

• GOOGLEFINANCE, GOOGLETRANSLATE, SPARKLINE with a different option syntax.

• ARRAYFORMULA itself.

Excel only, or newer there:

• XMATCH, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS and similar dynamic array helpers in Microsoft 365.

• GROUPBY and PIVOTBY in the newest builds.

• Data types such as Stocks and Geography.

• Functions tied to Power Query and the Data Model, like CUBEVALUE.

Both vendors keep adding functions, so some on these lists will have moved by the time you read this. The method is what lasts: look up each function in the other product's reference before you promise portability.

Errors and error handling

The error values mostly match: #DIV/0!, #N/A, #REF!, #VALUE!, #NAME?, #NUM!. Sheets adds #ERROR! for parse failures, and shows messages with detail when you hover. IFERROR and IFNA exist in both. The difference is in what triggers an error. Sheets is more forgiving in some coercions: adding a text that looks like a number to a number works in both, but comparisons of text and numbers, empty strings and zero, and boolean arithmetic show small differences. A formula tested in one should be tested in the other with blanks, text and zero as inputs.

An unsupported function in a file you open shows #NAME? in Excel and #ERROR! in Sheets, often with the original formula still visible in the formula bar, which at least tells you what was lost (the IFERROR family covers the handlers).

Dates and the epoch

Both store dates as serial numbers. Sheets counts days from December 30, 1899. Excel's 1900 date system counts from "January 0, 1900" and treats 1900 as a leap year by an inherited bug. For dates after March 1, 1900, the serial numbers agree, so a modern date is the same number in both (the 1900 leap year bug in Excel explains the history). Excel on the Mac used a 1904 system in older files, which shifts dates by 1,462 days (Mac and Windows date systems in Excel); Sheets does not offer that option.

Function names for dates agree, but the way dates are parsed from text depends on the locale of the file's owner in both, so DATEVALUE("03/04/2026") returns different days for different users. Prefer DATE(y, m, d) and ISO strings.

Locale and syntax

Both products store formulas in an English, comma-separated canonical form inside files and translate for display. Sheets lets you change the spreadsheet's locale in settings, which changes the decimal separator, the argument separator (comma or semicolon) and the date format. Excel takes these from the operating system. A formula pasted as text from a European Excel with semicolons will not parse in a comma locale. When writing formulas from code, write the canonical form, with commas and English function names (handling currency and locale in generated workbooks covers the same ground).

Text, case and sorting

Text comparison with = is case-insensitive in both. EXACT is case-sensitive in both. Sorting differs: Sheets sorts using the spreadsheet's locale rules, and Excel uses the system's, so accented characters and mixed case sort in different orders. Boolean sort order and the placement of blanks also differ. If a process depends on sort order, state it, and do the sort in the database with an explicit collation.

TEXT format strings are mostly shared, with differences for some codes, especially locale-specific ones and escape characters. Test any TEXT formula that produces a date or a currency in both.

Lookups and blanks

VLOOKUP, INDEX and MATCH work the same in practice. XLOOKUP exists in both now and differs at the edges in its optional arguments and wildcard handling. A frequent difference is how blanks are returned: a lookup that finds an empty cell returns 0 in Excel and an empty cell in Sheets in several cases, so downstream arithmetic or conditional formatting behaves differently (XLOOKUP examples that replace VLOOKUP pain covers the function itself).

What happens in a file conversion

Export from Sheets to xlsx keeps most formulas and converts what it can. Functions Excel lacks arrive as formulas that error, with the cached value shown until the first recalculation. Import from xlsx to Sheets keeps the cached values and translates formulas, with some features lost: certain chart types, Power Query connections, data model objects, sparklines with extra options and conditional formatting rules that use functions Sheets does not support.

A safe habit is to convert, recalculate, and compare totals across the two files before trusting the result. The control totals method used for CSV and workbooks applies here too (row counts and control totals).

Keeping a file portable

If a workbook must live in both, set rules:

• Use only functions in both products' references, and keep a list.

• Avoid ARRAYFORMULA, QUERY and IMPORT* in shared files; compute in the database and paste values.

• Write dates with DATE, not text.

• Avoid whole-column references in heavy formulas.

• Test the file by opening it in the other product and checking a set of control cells.

When the differences do not matter

They do not matter if the file stays in one product and is read by people there. They do not matter for formulas from the shared core. And they do not matter if the workbook is only a data container: when scripts read values and ignore formulas, the formula dialect is irrelevant, and you should be reading cached values or unformatted values anyway. Google's function list and Microsoft's Excel functions by category are the two lists to compare.