If you learned formulas in Excel, the function name alone tells you most of what you need to know about Google Sheets. The table below lines up each function with its counterpart, or with the replacement when there isn't one.
Search the Google Sheets functions list by Excel name
The three groups in the table (205 rows)
The table has three columns: the Excel function, the Google Sheets function, and what it does. Each row pairs one function with its counterpart in the other app.
SUMIF, COUNTIF and VLOOKUP work the same in Google Sheets
Google's help pages document 520 functions for Sheets, and 471 of them have the same name in Excel. The ones people use every day, such as SUMIF, COUNTIF, VLOOKUP, IF and TEXT, are all in that group. The table includes the 106 shared functions that come up most in office work.
Type a function name like VLOOKUP, or a task like "sum" or "translate", into the search box and only the matching rows stay. You can also filter by group with the tabs or sort by any column. Your search stays in your browser and isn't sent anywhere.
The descriptions are short rewrites of Google's and Microsoft's help pages. When a function has the same name but different arguments in each app, the description says so. CONCAT is the usual example: Excel lets it join whole ranges, while Google Sheets takes exactly two values.
Google's function list page shows 515 functions. Five more (XMATCH, BAHTTEXT, BINOM.DIST.RANGE, PERCENTIF and COUNTUNIQUEIFS) have their own help pages but are missing from that list. The table counts all 520.
A dash marks a Sheets-only or Excel-only function
A dash in the Google Sheets column means only Excel has that function. A dash in the Excel column means only Sheets has it. Pick the "Google Sheets only" or "Excel only" tab to see just one of those groups.
A missing function doesn't always mean a missing feature. Excel's TEXTSPLIT maps to SPLIT in Sheets, and Sheets' JOIN maps to TEXTJOIN in Excel.
When a replacement exists, the description names it, as in "in Excel use …" or "the closest Excel function is …". Rows with a dash sort to the bottom when you sort by a column.
Google Sheets supports cell formulas typically found in most desktop spreadsheet packages. — from the introduction to the function listView official source →
Google Sheets formulas that work the same as in Excel
Common functions by task (all exist in both apps)
Most functions with the same name also take their arguments in the same order, separated by commas inside the parentheses. The examples below can be typed into Google Sheets exactly as written.
SUMIF and COUNTIF in Google Sheets: totals by condition
SUMIF adds up only the numbers in rows that meet a condition. =SUMIF(B:B,"East",C:C) totals column C for the rows where column B says East. For two or more conditions, use SUMIFS, which puts the range to total first: =SUMIFS(C:C,B:B,"East",D:D,"Closed").
That order trips people up. The sum range comes last in SUMIF and first in SUMIFS, in both apps. When you turn a SUMIF into a SUMIFS, move the sum range to the front.
COUNTIF counts rows instead of adding them. The condition can be a comparison, so =COUNTIF(B2:B20,">=100") counts the values of 100 or more. AVERAGEIFS and MAXIFS follow the same pattern.
VLOOKUP, XLOOKUP and INDEX MATCH in Google Sheets
VLOOKUP searches the leftmost column of a range and returns a value from another column in the matching row. The range can sit on another sheet.
=VLOOKUP(A2,Items!A:D,3,FALSE) finds the value of A2 in column A of the Items sheet and returns the third column of that row. FALSE asks for an exact match, which is what you want for product codes and IDs.
XLOOKUP takes the column to search and the column to return as two separate ranges. =XLOOKUP(A2,Items!A:A,Items!C:C,"Not found") works even when the search column isn't on the left, and the fourth argument sets what to show when nothing matches. Because it points at columns rather than counting them, inserting a column doesn't break it.
INDEX MATCH still works too: =INDEX(Items!C:C,MATCH(A2,Items!A:A,0)). Keep the 0.
MATCH defaults to 1, which assumes a sorted range and returns the closest smaller value, so leaving it out can return the wrong row without any error. XMATCH, which Sheets also has, defaults to an exact match.
When a lookup finds nothing, VLOOKUP returns #N/A. For what each error code means, see "Excel errors: what #VALUE!, #N/A and #REF! mean".
TEXTJOIN, CONCATENATE and DATEDIF in Google Sheets
Text functions like LEFT, MID and SUBSTITUTE, and date functions like TODAY, EDATE and DATEDIF, take the same arguments in both apps. In either app, =DATEDIF(B2,TODAY(),"Y") returns the number of full years since the date in B2.
To join text from several cells, TEXTJOIN lets you set the separator. The formula =TEXTJOIN(", ",TRUE,A2:A10) joins A2 to A10 with a comma and a space, and TRUE skips empty cells.
CONCATENATE also works for any number of values. CONCAT takes only two values in Sheets, so an Excel formula that passes CONCAT a range or three values breaks there.
Returns a conditional sum across a range. — from the SUMIF entryView official source →
IMPORTRANGE, QUERY and other Google Sheets-only functions
Three kinds of functions only Google Sheets has
Google Sheets has 49 functions Excel lacks. Many of them exist because a Sheets file lives online. If you're coming from Excel, start with IMPORTRANGE, which reads another file, and with QUERY and ARRAYFORMULA, which handle a whole table in one formula.
IMPORTRANGE pulls a range from another Google Sheets file
IMPORTRANGE reads a range of cells from another spreadsheet into the one you have open. The first argument is the source file's URL and the second is the sheet name and range, both in double quotes: =IMPORTRANGE("URL of the source file","Sales!A1:D50"). Leave out the sheet name and it reads the first sheet.
The first time you connect to a new source, the cell shows #REF! and asks you to allow access. Click Allow access and the data loads. If someone else owns the source file, you need access to that file first.
Each request is capped at 10 MB. Importing a large table is slow, so it's faster to calculate the total in the source file and import just that cell.
Sheets must download the entire range to your computer and will be affected by slow network, and is capped at 10MB of received data per request. — from the section on external dataView official source →
The QUERY function and ARRAYFORMULA in Google Sheets
QUERY filters, groups and sorts a table using a short query written like a database request. =QUERY(A1:D100,"select A, sum(D) group by A",1) returns a table with column D totaled for each value in column A. The query goes in double quotes and refers to columns by letter, and the final 1 says the range has one header row.
The same language supports where, order by and pivot. A summary that would take a pivot table in Excel fits in one cell.
An array formula is a formula that returns results into more than one cell. ARRAYFORMULA applies one formula to a whole range: =ARRAYFORMULA(B2:B100*C2:C100) multiplies price by quantity for rows 2 to 100 at once, with no need to copy the formula down.
Pressing Ctrl+Shift+Enter while you edit a formula wraps it in ARRAYFORMULA for you. Functions that already return several cells, such as FILTER and SORT, spill into the neighboring cells without it.
Pressing Ctrl+Shift+Enter while editing a formula will automatically add ARRAYFORMULA( to the beginning of the formula. — from the notesView official source →
The GOOGLETRANSLATE formula in Google Sheets
GOOGLETRANSLATE translates the text in a cell. =GOOGLETRANSLATE(A2,"en","es") turns English in A2 into Spanish. Languages use two-letter codes, and "auto" as the source detects the language for you.
=GOOGLETRANSLATE(A2) with no language codes also works: it detects the source and translates into your system language. You can't drop only the source language, though. Either give both codes or neither.
GOOGLEFINANCE is another function tied to a Google service. Give it a ticker and it returns current or historical data from Google Finance. The closest Excel function is STOCKHISTORY.
If source_language is omitted, target_language must also be omitted. — from the syntaxView official source →
Excel-only functions and what to use in Google Sheets
What to do with the 50 Excel-only functions
Excel has 50 functions Google Sheets doesn't. A pasted formula that uses one of them won't calculate in Sheets. Most of the ones people run into have a rewrite, and the rest need the result stored as plain data instead.
Rewrite TEXTSPLIT as SPLIT and SORTBY as SORT
When you rewrite, check two things: the argument order, and what the function does when you leave an argument out. The table lists the rewrites people need most often.
| Excel | Google Sheets | What changes |
|---|---|---|
| TEXTSPLIT | SPLIT | SPLIT splits on each character of the delimiter by default |
| SORTBY | SORT | Descending is FALSE, not -1 |
| TRANSLATE | GOOGLETRANSLATE | Languages are two-letter codes |
| STOCKHISTORY | GOOGLEFINANCE | Different argument order |
| GROUPBY, PIVOTBY | QUERY | Write the summary with group by and pivot |
=TEXTSPLIT(A2,",") becomes =SPLIT(A2,","), and with a one-character delimiter the result is the same. With a longer delimiter it isn't.
SPLIT treats each character as its own delimiter, so ", " (comma and space) splits at every comma and every space. Add FALSE as the third argument to split on the whole string.
SORTBY sorts a range by a column outside it. SORT can do the same when its second argument is a column with the same number of rows.
The sort order is the other catch: SORTBY uses 1 and -1, while SORT uses TRUE and FALSE. A descending sort such as =SORTBY(A2:C20,D2:D20,-1) becomes =SORT(A2:C20,D2:D20,FALSE).
By default, each character in delimiter is considered individually, e.g. if delimiter is "the", then text is divided around the characters "t", "h", and "e". — from the delimiter argumentView official source →
PHONETIC, WEBSERVICE and CUBE have no Sheets version
PHONETIC pulls the furigana (the kana that shows how kanji are read) out of Japanese text, and DBCS and JIS turn half-width characters into full-width ones. Sheets has none of the three, so add a column with the values yourself.
WEBSERVICE and FILTERXML, which fetch web data and read XML, don't exist in Sheets either, and Microsoft notes they don't work in Excel for the web. In Sheets, IMPORTDATA reads CSV and TSV files from a URL, and IMPORTXML reads XML and HTML. The seven CUBE functions, which read from an OLAP cube (a server-side summary database), have no Sheets version.
If you move data between the two apps as CSV, "CSV to Excel: convert without losing leading zeros" covers the common pitfalls. For faster work in Excel itself, see the "Excel shortcut keys cheat sheet, Windows and Mac".
This function is not available in Excel for the web. — from the WEBSERVICE entryView official source →
FAQ
- Can I paste an Excel formula into Google Sheets?
- Yes, if every function in it exists in both apps, and most do. A formula that uses an Excel-only function such as TEXTSPLIT or SORTBY won't calculate in Sheets, so rewrite it with the replacement shown in the table. Watch for functions that share a name but not a behavior. CONCAT joins whole ranges in Excel, but in Google Sheets it takes exactly two values.
- Why does IMPORTRANGE show #REF!?
- The first time a sheet reads from a new source file, IMPORTRANGE shows #REF! with a prompt to connect the two files. Click Allow access and the data loads. If you don't own the source file, you need access to that file before the import can work.
Related articles
Date format cheat sheet: Python, PHP, Go and ExcelDate format codes for Python strftime, PHP date, Go layouts, JavaScript Intl options and Excel in one searchable table, with yyyymmdd and ISO 8601 examples.
Excel errors: what #VALUE!, #N/A and #REF! meanExcel errors each point to one kind of problem. Look up #VALUE!, #REF!, #N/A and more by code, get the fix, and see when IFNA beats IFERROR.
Claude Google Sheets, Docs and Slides add-on guideThe Claude Google Sheets add-on, Claude for Google Workspace, edits Sheets, Docs and Slides files in place on paid plans. Setup, edit modes and beta limits.
Excel vs Google Sheets: price, limits and macrosExcel vs Google Sheets on price, sharing, row limits and macros. Sheets is free and shares by link. Excel holds 1,048,576 rows a sheet and runs VBA.