For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Excel vs Google Sheets formulas: what actually translates?

CMP.A8 XLOOKUP/LET VS QUERY/REGEX
=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Mixed

Using the Excel FILTER Function to Create Dynamic Filters

Technology for Teachers and Students

Open on YouTube (opens in a new tab)
Technology for Teachers and Students

Loading preview…

If you can write formulas in one, you can write them in the other. SUM, IF, COUNTIFS, VLOOKUP, INDEX/MATCH, the text and date functions — same names, same argument order, same results. A workbook built from the common core moves between the two without anyone noticing.

The interesting question is the edges, and the edges are not where most people expect. The thing that actually breaks a file in practice is almost never a missing function.

The core that is genuinely identical

SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, IFS, AND, OR, NOT, IFERROR, COUNTIF(S), SUMIF(S), AVERAGEIF(S), VLOOKUP, HLOOKUP, INDEX, MATCH, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, SUBSTITUTE, TEXT, TODAY, NOW, DATE, EOMONTH, NETWORKDAYS, ROUND, ABS, MOD, RANK.

That list is most of what most people write. Build from it and compatibility is a non-issue.

XLOOKUP now exists in both, which it did not for years — so older “compatibility-safe” workbooks are full of INDEX/MATCH for a reason that has expired. XLOOKUP vs INDEX/MATCH covers when to migrate them.

One naming quirk inside the core: Excel’s TEXTJOIN is in Sheets, but Sheets’ JOIN has no Excel twin, and Sheets’ SPLIT returns an array where Excel’s TEXTSPLIT (newer, 365-only) does the same job under a different name.

Sheets-only, and worth knowing about

  • QUERY — a SQL-like language over a range in one formula: =QUERY(A:D, "select B, sum(D) where C = 'North' group by B order by sum(D) desc"). There is nothing like it in Excel. The nearest equivalent is FILTER + SORT + GROUPBY stacked, or a pivot table, or Power Query. For people who think in SQL, this alone keeps them in Sheets.
  • IMPORTRANGE — pulls a live range from another spreadsheet by URL, with a one-time permission prompt. Excel’s nearest is an external link (fragile, path-dependent) or Power Query against the file. Neither updates the way IMPORTRANGE does.
  • ARRAYFORMULA — applies a formula down a whole column without copying it. Modern Excel does not need a wrapper: dynamic array formulas spill natively. Translating ARRAYFORMULA to Excel usually means deleting it rather than replacing it.
  • GOOGLEFINANCE, GOOGLETRANSLATE, IMPORTHTML, IMPORTXML — web calls as worksheet functions. Excel reaches these only through Power Query or an add-in.
  • FLATTEN, ARRAY_CONSTRAIN, SPARKLINE.

Excel-only, and worth knowing about

  • LET — name intermediate results inside a formula, so a long expression stops repeating itself and starts being readable. Also faster, since a named calculation evaluates once.
  • LAMBDA plus MAP, REDUCE, SCAN, BYROW, BYCOL — define your own reusable functions in the grid, no script. Sheets has LAMBDA and the helper functions too now, but not the named-function workflow in the same form.
  • TAKE, DROP, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPROWS — the array-reshaping set. Sheets covers a little of this with ARRAY_CONSTRAIN and {} literals; the syntax does not match.
  • GROUPBY / PIVOTBY — pivot-style aggregation as a spilling formula.
  • The whole Power Query / Power Pivot / DAX layer. Sheets’ Connected Sheets and Apps Script solve related problems differently and with more setup — see Apps Script vs VBA.

One caveat on the Excel-only list: most of it is Microsoft 365 only. Excel 2019 and 2021 perpetual licences do not have LAMBDA, and XLOOKUP and dynamic arrays arrived in 2021. “Excel has it” is not the same as “your colleague’s Excel has it”, and that gap causes more real breakage than the Sheets comparison does.

What actually breaks

Here is the part people get wrong. A missing function fails loudly — #NAME?, visible, fixed in five minutes. The failures that cost you a day are silent.

1. Locale and separators. This is the big one. In a locale using comma as the decimal separator, formula arguments are separated by semicolons, not commas. =SUM(A1,A2) and =SUM(A1;A2) are both correct, in different places. Each application rewrites separators on open — usually correctly, occasionally not, and never visibly.

Worse is date parsing: 03/04/2026 is 3 April or 4 March depending on the locale the file is opened in. Nothing errors. A quarter’s dates land in the wrong months and you find out when the totals are queried. Set the file’s locale explicitly (Sheets: File → Settings; Excel: Options → Advanced), and store dates as real date values rather than text.

2. .xlsx ↔ Sheets round-trips lose things. Converting is lossy in both directions. Pivot tables are approximated, conditional formatting rules are simplified, slicers may not survive, data validation changes shape, macros are dropped entirely, and defined names can collide. The damage compounds if a file bounces back and forth.

3. Array semantics differ. Excel dynamic arrays spill into neighbouring cells and throw #SPILL! if blocked. Sheets’ ARRAYFORMULA and its spill behaviour are related but not identical, and a converted file can end up with results where Excel would have refused to write. The old Ctrl+Shift+Enter array formulas convert particularly badly.

4. Volatile-function performance. IMPORTRANGE in Sheets and heavy OFFSET/INDIRECT use in Excel both degrade big files, in different ways and at different thresholds. A model that is fine in one can be unusable in the other at the same row count.

The practical rule

Decide where the file lives, then write for that. The failure mode is not choosing a side: a workbook that half-commits to both ends up using neither tool’s good features and still breaking on locale.

If it must genuinely open cleanly in both — and that is rarer than people claim — stay inside the identical core listed at the top, store dates as dates, avoid pivot tables in the shared copy in favour of formula-driven summaries, and accept that the file will be plainer than either tool could manage alone.

If it lives in Sheets, use QUERY and IMPORTRANGE properly; they are the reason to be there.

If it lives in Excel, use LET, the array functions and Power Query properly; they are the reason to be there. And check which Excel version your readers have before you commit to LAMBDA.

For the collaboration side of the same decision — who edits, version control, comments — see Excel vs Google Sheets for teams.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Write formulas that are correct, readable, and resilient when the workbook changes. For this article, the goal is to practice: The core hundred functions are identical. What breaks when a file crosses over is rarely a missing function — it is locale, array behaviour, and the handful of genuinely one-sided features worth knowing by name.

Practice workbook setup

Create a small table with clean headers, sample edge cases, and expected outputs you can check manually.

Practice workflow

  1. Write the business rule in plain English before writing the formula.
  2. Build the formula in small pieces and test each piece.
  3. Use absolute references, table references, or named ranges intentionally.
  4. Test blanks, missing values, duplicate matches, and unexpected text.

Quality checks

  • The formula returns the expected result for normal and edge-case rows.
  • References point to stable ranges or tables.
  • Someone else can understand the formula after reading the labels around it.

Common mistakes

  • Fixing one row without testing the rest of the column.
  • Using approximate match or partial criteria without meaning to.
  • Letting hidden spaces or text numbers cause false mismatches.

Next actions

  • Create three test rows that should return different outcomes.
  • Compare the result with an alternate formula or manual filter.

Formula focus: test XLOOKUP(), FILTER(), LET() with normal rows, blank inputs, and at least one edge case.