Excel FormulasExcel and Google Sheets: where formulas differ
Excel and Google Sheets understand almost the same formula language. A sheet full of SUM, IF, VLOOKUP and COUNTIF works in both without a change. The differences are few, and they matter mostly when a file is moved from one program to the other. This page lists the ones worth knowing.
It is written from general knowledge of the two programs and from testing the Excel side. Both programs change often, so check any point that matters on your own version.
What is the same
The everyday functions carry the same names and arguments in both programs:
- Maths and counting: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, ROUND, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, SUMPRODUCT.
- Logic: IF, IFS, AND, OR, NOT, IFERROR, IFNA, SWITCH.
- Lookup: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, XMATCH, CHOOSE.
- Text: LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, SUBSTITUTE, FIND, SEARCH, TEXT, VALUE, TEXTJOIN.
- Dates: TODAY, NOW, DATE, YEAR, MONTH, DAY, WEEKDAY, EDATE, EOMONTH, DATEDIF, NETWORKDAYS, WORKDAY.
So are the basics: the equals sign, the operators, the dollar sign for fixed references, and the error values.
Functions only in Google Sheets
| Function | What it does |
|---|---|
| QUERY | selects, filters, groups and sorts a range using a query language similar to SQL |
| ARRAYFORMULA | makes an ordinary formula work on a whole range at once |
| IMPORTRANGE | brings in a range from another spreadsheet file |
| IMPORTHTML, IMPORTXML, IMPORTDATA | pull tables and data from web pages |
| GOOGLEFINANCE | share prices and currency rates |
| GOOGLETRANSLATE | translates text |
| REGEXMATCH, REGEXEXTRACT, REGEXREPLACE | pattern matching in text |
| SPLIT | splits text at a separator into several cells |
| JOIN | joins a range with a separator |
| SPARKLINE | a tiny chart inside a cell, made by a formula |
| IMAGE | shows a picture from a web address in a cell |
A workbook that uses these loses them when saved as an Excel file. The cells keep their last values, or show an error.
Functions and features only in Excel
- Power Query and PivotTables on a larger scale, with data models. Sheets has pivot tables, with fewer options.
- AGGREGATE, which totals while skipping errors and hidden rows.
- Tables with structured references, such as
=SUM(Sales[Amount]). Sheets has added tables of its own, with different behaviour. - Newer Microsoft 365 functions such as TEXTSPLIT, TEXTBEFORE and TEXTAFTER. Sheets uses SPLIT and the REGEX functions for those jobs.
- Macros in VBA. Sheets uses Apps Script, a different language. Macros do not transfer.
Arrays: the biggest practical difference
In Excel 2021 and Microsoft 365, a formula that produces several results spills into neighbouring cells by itself. =C2:C9*2 typed in one cell fills eight.
In Google Sheets, most ordinary formulas need to be wrapped in ARRAYFORMULA to do that: =ARRAYFORMULA(C2:C9*2). Pressing Ctrl, Shift and Enter after typing adds the wrapper for you. Functions that are array functions by nature, such as FILTER, SORT and UNIQUE, spill in Sheets without it.
Referring to a spilled range also differs. Excel has the hash sign: H2# means everything that the formula in H2 spilled. Sheets has no such sign. People there use open-ended ranges such as H2:H.
Open-ended ranges
Sheets accepts a range with no end row, such as A2:A, meaning "from A2 to the bottom of the column". Excel does not accept that form. The nearest Excel equivalents are a whole column, A:A, or a table column.
FILTER and SORT have different shapes
| Task | Excel | Google Sheets |
|---|---|---|
| Rows meeting two conditions | =FILTER(A2:F13,(C2:C13="North")*(D2:D13="Bag")) | =FILTER(A2:F13,C2:C13="North",D2:D13="Bag") |
| A message when nothing matches | third argument of FILTER | wrap in IFERROR(..., "None") |
| Sort by the third column, descending | =SORT(A2:F13,3,-1) | =SORT(A2:F13,3,FALSE) |
| Sort by a range outside the data | SORTBY | SORT accepts a range as the sort column |
Excel multiplies conditions together. Sheets lists them with commas. Excel's sort direction is 1 or -1. Sheets uses TRUE or FALSE.
Smaller differences
- CONCAT joins any number of values and ranges in Excel. In Sheets it joins exactly two. Use CONCATENATE,
&or TEXTJOIN in Sheets. - IFERROR needs its second argument in Excel. In Sheets it is optional and defaults to blank.
- ROUND needs the number of places in Excel. In Sheets it may be left out.
- DATEDIF is undocumented in Excel and fully documented in Sheets.
- Text that looks like a number is treated more strictly by Excel. Sheets often converts it silently in arithmetic.
- Case in comparisons: both ignore case in
=, VLOOKUP and COUNTIF. - Wildcards work in the same functions in both.
Separators and regional settings
In both programs, the character between arguments depends on the region. Where a comma is the decimal mark, as in much of Europe, formulas use a semicolon between arguments: =IF(A1>5;"Yes";"No"). In India, the United Kingdom and the United States it is a comma. A formula copied from a website may need its separators changed. In Excel this follows the Windows regional settings. In Sheets it follows the spreadsheet's locale, under File, Settings.
Moving a file between the two
Excel to Sheets. Upload the file to Google Drive and open it. Ordinary formulas convert. Macros, Power Query, and some charts and formatting do not. Recent Microsoft 365 functions that Sheets lacks show errors.
Sheets to Excel. Use File, Download, Microsoft Excel. Ordinary formulas convert. The Sheets-only functions in the table above are replaced by their last values or by errors. ARRAYFORMULA wrappers are usually handled, since modern Excel spills by itself.
After any move, look for cells showing #NAME?, compare a few totals with the original, and check the dates.
Shortcuts and working habits
- Fixing a reference with dollar signs is F4 in both.
- Excel's Flash Fill (Ctrl and E) has a counterpart in Sheets' Smart Fill.
- Sheets saves by itself and keeps a version history. Excel does the same for files stored in OneDrive.
- Several people can edit a Sheets file together in a browser. Excel offers this for files in OneDrive or SharePoint.
Which to learn?
The skills carry across. Someone who can write IF, VLOOKUP, SUMIFS and a date formula in one program can do it in the other within an hour. Learn on whichever one your school, college or office uses, and keep this page for the day you switch.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.