Cellwise Start free

Excel guide · 10 min read

Excel vs Google Sheets formulas: what works in both and what breaks

Quick answerMost everyday formulas are written the same way and give the same result in both: SUM, SUMIFS, COUNTIFS, IF, VLOOKUP, XLOOKUP, INDEX and MATCH, TEXT and the date functions. The differences people hit are arrays (Excel 365 spills a range calculation on its own, Sheets needs ARRAYFORMULA), Sheets-only functions such as QUERY, IMPORTRANGE and GOOGLEFINANCE, a few functions that share a name but not their arguments (FILTER, SORT, SPLIT vs TEXTSPLIT), and files moved between the two, which lose macros and any function the other app doesn't have.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDE
1Order dateCustomerRegionQtyUnit price
2Nov 2, 2026Brightside CafeWest124.5
3Nov 3, 2026Oak DentalEast618
4Nov 4, 2026Hill & CoWest203.25
5Nov 5, 2026Lumen StudioNorth422
6Nov 6, 2026Riverbend GymWest89.75
7Nov 9, 2026Brightside CafeWest154.5
8Nov 10, 2026Oak DentalEast1018
9Nov 11, 2026Pine BakeryNorth252.8

Download this sheet as an Excel practice file (.xlsx)

Formulas that work the same in both

If you only use the core functions, you can paste a formula from one app into the other and it works. The order table above is a good test: type it into both and these give identical results.

Both apps also share the newer lookup and naming functions, but in Excel they depend on your version. Google Sheets has them for everyone because it's always the current version. In Excel, check before you send a file to someone on an older copy: a function their version lacks shows #NAME?.

  • Maths and counting: SUM, SUMIF(S), COUNTIF(S), AVERAGEIF(S), SUMPRODUCT, ROUND, MIN, MAX, LARGE, SMALL.
  • Logic: IF, IFS, AND, OR, NOT, IFERROR, IFNA, SWITCH.
  • Lookups: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, XMATCH.
  • Text: LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, TRIM, UPPER, PROPER, TEXT, TEXTJOIN and the & operator.
  • Dates: DATE, TODAY, EDATE, EOMONTH, WEEKDAY, NETWORKDAYS, DATEDIF.
  • XLOOKUP, XMATCH, FILTER, UNIQUE, SORT and LET need Excel 365, Excel 2021 or later. LAMBDA and its helpers (MAP, BYROW, REDUCE...) need Excel 365 or Excel 2024. Excel 2019 and older have none of them.
=XLOOKUP("Oak Dental",B2:B9,E2:E9,"Not found")

Result: 18

18: the unit price of the first Oak Dental order. Same arguments and same result in Excel 365 and Google Sheets.

Arrays: Excel spills, Sheets wants ARRAYFORMULA

In Excel 365 and 2021, a calculation on whole ranges spills on its own. Type =D2:D9*E2:E9 in one cell and Excel fills eight cells with the line totals. If something is in the way you get #SPILL!; see fix the #SPILL! error.

In Google Sheets the same formula doesn't fill the column. Wrap it as =ARRAYFORMULA(D2:D9*E2:E9) (pressing Ctrl+Shift+Enter while editing adds the wrapper for you). Functions that return arrays by design, such as FILTER, UNIQUE, SORT and SEQUENCE, expand in Sheets without the wrapper. When an array result is blocked, Sheets shows #REF! with "Array result was not expanded because it would overwrite data" rather than #SPILL!.

Excel has no ARRAYFORMULA function, so a formula copied from Sheets with the wrapper needs it removed in Excel 365. In the other direction, Excel's spilled range reference (E2# for "everything E2 spilled") is not something Sheets understands: point at a plain range such as E2:E9 instead.

For a single total, SUMPRODUCT is the portable choice: =SUMPRODUCT(D2:D9,E2:E9) gives 710.50 in both apps, with no wrapper in Sheets and no Ctrl+Shift+Enter in old Excel. See SUMPRODUCT in Excel.

=D2:D9*E2:E9

Result: spills 8 rows × 1 column

54
108
65
88
78
67.5
180
70

Excel 365 spills 8 line totals: 54, 108, 65, 88, 78, 67.5, 180 and 70. In Google Sheets, write =ARRAYFORMULA(D2:D9*E2:E9) for the same result.

Same name, different arguments: FILTER, SORT, SPLIT

FILTER is the one that catches people. In Excel it is FILTER(array, include, [if_empty]): the third argument is what to show when nothing matches. In Sheets it is FILTER(range, condition1, [condition2, ...]): every extra argument is another condition. So the Excel formula =FILTER(B2:B9,C2:C9="South","None") is an error in Sheets, because "None" isn't a condition. When nothing matches, Excel shows #CALC! (or your if_empty text) and Sheets shows #N/A; in Sheets wrap it in IFNA to show your own message.

To write one FILTER that works in both, combine the conditions yourself by multiplying them, as below, and leave out the third argument. More patterns are in FILTER with multiple criteria.

SORT: Excel's is SORT(array, [sort_index], [sort_order], [by_col]) with 1 or -1 for the order. Sheets' is SORT(range, sort_column, is_ascending, ...) with TRUE or FALSE, and any further pairs are extra sort columns. Translate -1 to FALSE when you move a descending sort to Sheets, and don't carry Excel's fourth by_col argument across. See sort data with a formula.

Splitting text: Sheets has SPLIT(text, delimiter). By default SPLIT treats each character of the delimiter as a separate delimiter, so SPLIT(A2,", ") splits on every comma and every space; set its third argument to FALSE to split on the whole ", ". Excel 365 uses TEXTSPLIT(text, col_delimiter), which always splits on the whole delimiter. Older Excel has Data > Text to Columns; see split text by delimiter.

Regular expressions: Sheets has REGEXMATCH, REGEXEXTRACT and REGEXREPLACE. Microsoft 365 added REGEXTEST, REGEXEXTRACT and REGEXREPLACE, so the "does it match" function has a different name, and older Excel versions have none of them.

=FILTER(B2:B9,(C2:C9="West")*(D2:D9>=10))

Result: spills 3 rows × 1 column

Brightside Cafe
Hill & Co
Brightside Cafe

3 customers: Brightside Cafe, Hill & Co and Brightside Cafe again (West orders of 10 or more). The same formula works in Excel 365 and Google Sheets.

Sheets-only functions and what to use in Excel

These Google Sheets functions have no function of the same name in Excel. Each one has an Excel way to get the same job done, usually a feature rather than a formula.

  • QUERY runs a SQL-like statement over a range, for example =QUERY(A1:E9,"select B, sum(D) where C = 'West' group by B",1). In Excel use SUMIFS for one total, FILTER and SORT for a list, a PivotTable for a summary, or GROUPBY and PIVOTBY in current Microsoft 365.
  • IMPORTRANGE pulls a range from another Google spreadsheet by its URL, after a one-time Allow access prompt. In Excel, link to the other workbook (='[Budget.xlsx]Sheet1'!B4) or load it with Data > Get Data (Power Query). Linked workbooks have their own catch: SUMIF and COUNTIF on a closed source workbook return #VALUE!. See break links in Excel.
  • GOOGLEFINANCE returns current or historical prices, for example =GOOGLEFINANCE("NASDAQ:GOOG","price"). In Microsoft 365, use the Stocks data type (Data > Stocks) for current quotes and STOCKHISTORY for history. Both apps' market data can be delayed, so don't treat either as a trading feed.
  • IMPORTHTML, IMPORTXML, IMPORTDATA and IMPORTFEED read tables, pages or CSV files from the web. Excel does this with Data > From Web (Power Query), which refreshes on demand rather than as a formula.
  • SPARKLINE draws a tiny chart from a formula. Excel has sparklines as a feature instead: Insert > Sparklines.
  • ARRAYFORMULA isn't needed in Excel 365 (ranges spill on their own), and FLATTEN's job is done by TOCOL, which both apps have.

Dates, time zones and regional separators

Both apps store a date as a serial number, so date maths works the same: =A9-A2 is 9 days in either. For any date from March 1, 1900 onwards the serial number is identical too, so 2 November 2026 is 46328 in both. The two only differ in the very early days of 1900: Excel counts January 1, 1900 as day 1 and includes a February 29, 1900 that never existed, while Sheets counts from December 30, 1899. Sheets also accepts dates before 1900; Excel stores those as text.

Some workbooks created on older Macs use Excel's 1904 date system (File > Options > Advanced > Use 1904 date system), where every serial number is 1,462 smaller. Copying dates between a 1904 workbook and a normal one shifts them by 4 years and 1 day.

TODAY and NOW follow your computer's clock in Excel. In Sheets they follow the spreadsheet's own time zone in File > Settings, which may not be yours if someone else created the file. Sheets also has a recalculation setting there (on change, every minute or every hour) for NOW, TODAY, RAND and RANDBETWEEN.

Typed dates and separators follow the locale. Excel uses your Windows or Mac regional settings; Sheets uses File > Settings > Locale of each spreadsheet. In locales where the decimal mark is a comma, both apps separate arguments with a semicolon, so =ROUND(A2;2) is the same formula as =ROUND(A2,2). You don't need to rewrite formulas when a file moves between locales: each app converts them for display. Text dates are another matter: "03/11/2026" is March 11 in a US locale and 3 November in a UK one. See convert text to date.

=A2*1

Result: 46328

46328: the serial number of 2 November 2026, the same in Excel and Google Sheets.

Opening an Excel file in Google Sheets: what breaks

You can open an .xlsx from Google Drive and either edit it as an Excel file or save a copy as a Google Sheet (File > Save as Google Sheets). Plain data and the shared functions above come across fine. Check these before you rely on the result:

  • VBA macros don't run in Google Sheets. Sheets automation is Apps Script, which is a different language, so macros have to be rewritten. See enable macros in Excel for the Excel side.
  • Power Query queries, Power Pivot data models and external data connections don't refresh in Sheets. You get the last values that were saved.
  • Linked data types (Stocks, Geography) and form or ActiveX controls don't work in Sheets.
  • Any Excel function Sheets doesn't have shows #NAME?. That includes STOCKHISTORY and, often, Excel 365's newest functions. Spilled range references such as E2# need rewriting as normal ranges.
  • Formulas that use table names like Orders[Qty] and links to other workbooks on your PC are the parts to check first: Sheets can't reach a file on your hard drive.
  • Size: a Google spreadsheet holds up to 10 million cells in total, while each Excel worksheet has 1,048,576 rows by 16,384 columns. A big Excel workbook may not convert at all.

Downloading a Google Sheet as Excel: what breaks

File > Download > Microsoft Excel (.xlsx) keeps the data, the formatting and every formula that Excel also has. The rest needs work:

  • Sheets-only functions (QUERY, IMPORTRANGE, GOOGLEFINANCE, SPARKLINE...) can't calculate in Excel. The download keeps their last values, and the formula bar shows something like =__xludf.DUMMYFUNCTION("QUERY(...)"). The numbers look right until the source changes, then they never update. Rebuild them with the Excel equivalents above.
  • ARRAYFORMULA ranges and Sheets' own FILTER or SORT arguments may need rewriting in Excel's form (see the FILTER and SORT section above).
  • Apps Script doesn't come along, and neither does anything that depends on Google, such as form responses or connected sheets.
  • If the people opening the file have Excel 2019 or older, XLOOKUP, FILTER, UNIQUE, LET and friends show #NAME? there even though they worked in Sheets.

Writing formulas that work in both

If a workbook will be used in both apps, stick to the shared functions, avoid Excel's spilled range references and Sheets' ARRAYFORMULA, and combine FILTER conditions with * (AND) or + (OR) instead of using FILTER's extra arguments. LET is safe in both and keeps long formulas readable: this one works out the average order value from the same table.

Joining a list is portable too: =TEXTJOIN(", ",TRUE,UNIQUE(C2:C9)) returns "West, East, North" in Excel 365 and Sheets. If you only need one app, use its strengths: QUERY and IMPORTRANGE in Sheets, Power Query, PivotTables and the full desktop feature set in Excel. Which formulas to learn first is the same for both, so most of what this site teaches with the shared functions works the same way in Sheets.

=LET(total,SUMPRODUCT(D2:D9,E2:E9),orders,COUNTA(B2:B9),total/orders)

Result: 88.8125 shows as $88.81 with Currency format

88.8125, shown as $88.81: average order value (710.50 over 8 orders), the same in Excel 365 and Google Sheets.

Asked to explain this in an interview? See model answers to 30 Excel interview questions, or take the free Excel practice test.

Common mistakes

Copying =FILTER(range,condition,"None") from Excel to Sheets

In Sheets the third argument is another condition, so it errors. Drop it and wrap the formula in IFNA(...,"None") instead.

Typing =D2:D9*E2:E9 in Sheets and expecting it to fill the column

Wrap it in ARRAYFORMULA in Sheets, or use SUMPRODUCT when you only need the total.

Downloading a Sheet with QUERY or GOOGLEFINANCE as .xlsx and trusting the numbers

Those cells are frozen at their last value in Excel. Rebuild them with SUMIFS, FILTER, a PivotTable or the Stocks data type.

Sending a file built in Sheets to someone on Excel 2019

XLOOKUP, FILTER, UNIQUE and LET show #NAME? there. Use INDEX/MATCH, SUMIFS and COUNTIFS for files that must open in older Excel.

FAQ

Do Excel formulas work in Google Sheets?

Most do, with the same syntax and results: SUM, SUMIFS, COUNTIFS, IF, VLOOKUP, XLOOKUP, INDEX/MATCH, TEXT and the date functions. Formulas that use whole-range maths need ARRAYFORMULA in Sheets, FILTER and SORT take different arguments, and functions Sheets lacks show #NAME?.

Is ARRAYFORMULA the same as an Excel array formula?

It does the same job. In Excel 365 and 2021 you don't need anything: a range calculation spills on its own. Older Excel needed Ctrl+Shift+Enter, which put braces around the formula. In Sheets, Ctrl+Shift+Enter adds ARRAYFORMULA( ) around it.

What is the Excel equivalent of QUERY?

There is no single function. Use SUMIFS or COUNTIFS for one result, FILTER and SORT for a list of rows, a PivotTable for a grouped summary, GROUPBY or PIVOTBY in current Microsoft 365, or Power Query (Data > Get Data) for a repeatable import and clean-up.

Does Google Sheets have XLOOKUP and LAMBDA?

Yes. Sheets has XLOOKUP, XMATCH, LET and LAMBDA, plus Named functions (Data > Named functions) to save a LAMBDA under a name. In Excel, XLOOKUP and LET need Excel 365 or 2021 and later, and LAMBDA needs Excel 365 or 2024. See VLOOKUP vs XLOOKUP.

Why does my formula use semicolons instead of commas?

Your locale uses a comma as the decimal mark, so both Excel and Google Sheets separate arguments with semicolons. The formula is the same; each app shows it in the separators of its own locale setting.

Functions used

Related guides

Updated