Excel vs Google Sheets formulas: what actually translates?
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

XLOOKUP Complete Tutorial in Excel (+ Hidden Tricks)
MyOnlineTrainingHub
Open on YouTube (opens in a new tab)
Dynamic Arrays in Excel The Complete Guide (FILTER, SORT, UNIQUE)
Sheet Genius
Open on YouTube (opens in a new tab)
Using the Excel FILTER Function to Create Dynamic Filters
Technology for Teachers and Students
Open on YouTube (opens in a new tab)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 isFILTER+SORT+GROUPBYstacked, 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 wayIMPORTRANGEdoes.ARRAYFORMULA— applies a formula down a whole column without copying it. Modern Excel does not need a wrapper: dynamic array formulas spill natively. TranslatingARRAYFORMULAto 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.LAMBDAplusMAP,REDUCE,SCAN,BYROW,BYCOL— define your own reusable functions in the grid, no script. Sheets hasLAMBDAand 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 withARRAY_CONSTRAINand{}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.
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
- Write the business rule in plain English before writing the formula.
- Build the formula in small pieces and test each piece.
- Use absolute references, table references, or named ranges intentionally.
- 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.






