Google Sheets Formulas Cheat Sheet
Every Google Sheets formula worth knowing, in one place. It starts with the Sheets-only functions that Excel simply does not have, then walks through the everyday math, text, date, lookup, logic, and counting formulas. Copy the syntax, paste it into your sheet, and get back to work. Grab the printable PDF below if you want it next to your monitor.
The formulas, grouped by what you are trying to do
| What you want | Formula | Notes |
|---|---|---|
| Apply one formula down a whole column | =ARRAYFORMULA(A2:A * B2:B) | Write it once and it fills every row, including rows you add later. See the full ARRAYFORMULA guide. |
| Run a SQL-style query on your sheet | =QUERY(A1:D, "select A, sum(D) where C = 'Open' group by A", 1) | Filter, sort, group, and total in one statement. The single most powerful thing Sheets does that Excel cannot. |
| Pull a range from another spreadsheet | =IMPORTRANGE("sheet_url", "Sheet1!A1:D100") | Approve access once and the link stays live. Full walkthrough: IMPORTRANGE for beginners. |
| Live stock and currency prices | =GOOGLEFINANCE("GOOG", "price") | Real-time and historical market data straight into a cell. There is no Excel equivalent. |
| A tiny chart inside one cell | =SPARKLINE(A2:A12, {"charttype","column"}) | Line, bar, or column. Perfect for a trend column in a dashboard. |
| Scrape a table or list from a web page | =IMPORTHTML("url", "table", 1) | Second argument is "table" or "list". The number picks which one on the page. |
| Import a CSV or TSV from a URL | =IMPORTDATA("https://example.com/data.csv") | Refreshes on its own schedule. Handy for public data feeds. |
| Pull any element from a page with XPath | =IMPORTXML("url", "//h2") | When IMPORTHTML is too blunt, XPath grabs the exact node you want. |
| Translate text in place | =GOOGLETRANSLATE(A1, "en", "es") | No add-on required. Works on a whole column when wrapped in ARRAYFORMULA. |
| Collapse a 2-D range into one column | =FLATTEN(A1:C10) | Sheets only. Useful right before UNIQUE or COUNTIF. |
| Match, extract, or replace with regex | =REGEXEXTRACT(A1, "\d+") =REGEXMATCH(A1, "@") =REGEXREPLACE(A1, "\s+", " ") | Regex built into the formula bar. Excel makes you write a macro for this. |
| What you want | Formula | Notes |
|---|---|---|
| Add up a range | =SUM(A2:A100) | The one everybody starts with. Works the same in Excel. |
| Add only the rows that match one condition | =SUMIF(B:B, "Open", A:A) | Test range first, condition second, the range you are adding third. |
| Add with several conditions | =SUMIFS(A:A, B:B, "Open", C:C, "West") | Sum range comes first here, which trips people up. See SUMIFS in Google Sheets. |
| Multiply two columns and total the result | =SUMPRODUCT(A2:A20, B2:B20) | Quantity times price, totalled, without a helper column. |
| Multiply | =A2*B2 =PRODUCT(A2:A10) | Both work. More ways to do it: how to multiply in Google Sheets. |
| Round a number | =ROUND(A2, 2) =ROUNDUP(A2, 0) =ROUNDDOWN(A2, 0) | Second argument is decimal places. Use 0 for whole numbers. |
| Smallest and largest value | =MIN(A:A) =MAX(A:A) | Ignores text and blanks automatically. |
| Total that ignores filtered-out rows | =SUBTOTAL(109, A2:A100) | 109 means sum while skipping hidden rows. 101 does the same for average. |
| What you want | Formula | Notes |
|---|---|---|
| Join two cells together | =A2&" "&B2 =CONCATENATE(A2," ",B2) | The ampersand is faster to type and reads better. |
| Join a whole range with a separator | =TEXTJOIN(", ", TRUE, A2:A20) | TRUE skips the blanks. This is the one you want for comma-separated lists. |
| Split one cell into many | =SPLIT(A2, ",") | Spills across the columns to the right, so leave them empty. |
| Grab characters from the start, end, or middle | =LEFT(A2, 3) =RIGHT(A2, 4) =MID(A2, 3, 5) | MID takes a start position and a length. |
| Count the characters in a cell | =LEN(A2) | Counts spaces too, which is usually the bug you are hunting. |
| Strip extra spaces and junk | =TRIM(A2) =CLEAN(A2) | TRIM kills double spaces, CLEAN removes non-printing characters from pasted data. |
| Change the case | =UPPER(A2) =LOWER(A2) =PROPER(A2) | PROPER capitalizes each word, which is handy for names. |
| Find and replace inside a cell | =SUBSTITUTE(A2, "-", " ") | Replaces text by value. REPLACE swaps by position instead. |
| Find where a bit of text starts | =SEARCH("cat", A2) =FIND("cat", A2) | SEARCH ignores case, FIND does not. Both return a position number. |
| Format a number as text | =TEXT(A2, "$#,##0.00") | Great for building clean labels and email merges. |
| What you want | Formula | Notes |
|---|---|---|
| Today, and right now | =TODAY() =NOW() | Both refresh automatically. TODAY is a date, NOW includes the time. |
| Build a date from parts | =DATE(2026, 7, 12) | Year, month, day. Use this instead of typing a date as text. |
| Move a date forward or back by months | =EDATE(A2, 3) | Negative numbers go backwards. Perfect for renewal dates. |
| Last day of the month | =EOMONTH(A2, 0) | 0 is this month, 1 is next month, -1 is last month. |
| Business days between two dates | =NETWORKDAYS(A2, B2) | Skips weekends. Add a holiday range as a third argument. |
| Age, or the gap between two dates | =DATEDIF(A2, B2, "Y") | Use "Y", "M", or "D" for years, months, or days. |
| Pull out the year, month, or day | =YEAR(A2) =MONTH(A2) =DAY(A2) | Useful for grouping a report by month. |
| Which day of the week | =WEEKDAY(A2, 2) | Type 2 makes Monday day 1, which is what most people expect. |
| What you want | Formula | Notes |
|---|---|---|
| Classic vertical lookup | =VLOOKUP(A2, Data!A:D, 4, FALSE) | FALSE forces an exact match. Always use FALSE. See the VLOOKUP guide. |
| The modern replacement for VLOOKUP | =XLOOKUP(A2, B:B, C:C, "Not found") | Looks left or right, exact match by default, built-in not-found message. See XLOOKUP in Google Sheets. |
| Flexible two-way lookup | =INDEX(C:C, MATCH(A2, B:B, 0)) | Survives column moves, unlike VLOOKUP. See INDEX and MATCH. |
| Look across a row instead of down a column | =HLOOKUP(A2, Data!1:5, 3, FALSE) | Same idea as VLOOKUP, rotated 90 degrees. |
| Return every row that matches | =FILTER(A2:D100, C2:C100="Open") | Not just the first match, all of them. See the FILTER function. |
| Sort a range with a formula | =SORT(A2:C50, 2, FALSE) | Column 2, descending. The source data stays untouched. |
| Strip out duplicates | =UNIQUE(A2:A200) | Pairs beautifully with COUNTIF to build a quick frequency table. |
| A lookup with filtering built in | =QUERY(A:D, "select D where A = '"&F2&"'", 0) | When a lookup needs conditions, QUERY beats nesting functions. |
| What you want | Formula | Notes |
|---|---|---|
| Do one thing or another | =IF(A2>0, "Yes", "No") | Test, value if true, value if false. See the IF function guide. |
| Several tests without nesting | =IFS(A2>90, "A", A2>80, "B", A2>70, "C") | Reads top to bottom and stops at the first match. |
| Combine conditions | =AND(A2>0, B2="Open") =OR(A2>0, B2="Open") =NOT(A2=0) | Usually lives inside an IF as the test. |
| Hide an ugly error | =IFERROR(A2/B2, 0) | Catches every error type. See IFERROR in Google Sheets. |
| Catch only a not-found error | =IFNA(VLOOKUP(A2, B:C, 2, FALSE), "Missing") | Safer than IFERROR because real errors still show up. |
| Map a value to a result | =SWITCH(A2, "N", "North", "S", "South", "Other") | Cleaner than a stack of IFs when you are just translating codes. |
| Check what is in a cell | =ISBLANK(A2) =ISNUMBER(A2) =ISTEXT(A2) =ISEMAIL(A2) | ISEMAIL is a Sheets extra. All return TRUE or FALSE. |
| What you want | Formula | Notes |
|---|---|---|
| Count the numbers | =COUNT(A2:A100) | Counts numbers only. Text and blanks are ignored. |
| Count everything that is not empty | =COUNTA(A2:A100) | Counts text too. See how to count cells with text. |
| Count what matches one condition | =COUNTIF(A:A, ">100") | Conditions go in quotes, even the comparisons. See the COUNTIF guide. |
| Count with several conditions | =COUNTIFS(A:A, ">100", B:B, "Open") | Every condition has to be true for the row to count. |
| Count the blanks | =COUNTBLANK(A2:A100) | The fastest way to find gaps in your data. |
| Count distinct values | =COUNTUNIQUE(A2:A100) | Sheets only. In Excel you would need a SUMPRODUCT trick. |
| Average a range | =AVERAGE(A2:A100) | Skips blanks, which is usually what you want. |
| Average only the matching rows | =AVERAGEIF(B:B, "Open", A:A) =AVERAGEIFS(A:A, B:B, "Open", C:C, "West") | Same argument order quirk as SUMIF and SUMIFS. |
| The middle value, or the most common one | =MEDIAN(A2:A100) =MODE(A2:A100) | Median beats average when a few outliers are skewing things. |
| Rank, or the Nth biggest | =RANK(A2, A:A) =LARGE(A:A, 3) =SMALL(A:A, 3) | LARGE and SMALL are how you build a top-five list. |
Get the printable PDF
We will email you the Google Sheets Formulas Cheat Sheet as a printable PDF, yours to keep. No cost, and you can unsubscribe any time.
How to use this cheat sheet
Work from the top down. The Sheets-only power functions are first on purpose, because they are the ones that change how you build a spreadsheet. ARRAYFORMULA, QUERY, IMPORTRANGE, GOOGLEFINANCE, SPARKLINE, IMPORTHTML, and IMPORTDATA have no direct Excel equivalent, so if you are coming from Excel this is the part you have never seen. Learn those six or seven and you are already doing things most Sheets users cannot.
Everything below that group carries over cleanly. SUM, SUMIFS, IF, IFERROR, VLOOKUP, XLOOKUP, INDEX, MATCH, TEXTJOIN, COUNTIF, and the date functions all behave the same way in Excel, so the muscle memory transfers in both directions. The things that do not carry over are the Google-specific ones: the IMPORT family, GOOGLEFINANCE, GOOGLETRANSLATE, SPARKLINE, FLATTEN, COUNTUNIQUE, and QUERY. If you need the Excel side too, the cheat sheets below cover it. New to all of this? Start with how to use Google Sheets, then the full Google Sheets formulas and Google Sheets functions tutorials.
Go deeper
Excel Cheat Sheet
The full Excel reference: formulas, shortcuts, and formatting in one place. Start here if you split your time between the two apps.
Open the Excel cheat sheetExcel Shortcuts Cheat Sheet
The keyboard shortcuts that actually save time, sorted by what you are doing. Most of them work in Sheets too.
Open the shortcuts cheat sheetExcel Formulas Cheat Sheet
The Excel side of this page. Same categories, same copy-paste syntax, written for Excel instead of Sheets.
Open the formulas cheat sheetExcel Lookup Functions Cheat Sheet
VLOOKUP, XLOOKUP, INDEX and MATCH, side by side, with the trade-offs spelled out so you pick the right one.
Open the lookup cheat sheetQuestions people ask
Is this Google Sheets cheat sheet really free?
Yes. Every formula on this page is free to read and free to use, and the printable PDF version is free as well. Enter your email in the form above and we will send it over. There is no card, no trial, and you can unsubscribe any time.
Do these formulas work in Excel too?
Most of them do. SUM, SUMIFS, IF, IFERROR, VLOOKUP, XLOOKUP, INDEX, MATCH, TEXTJOIN, COUNTIF, and the date functions all behave the same way in Excel. The exception is the Sheets-only group at the top of this page. ARRAYFORMULA, QUERY, IMPORTRANGE, GOOGLEFINANCE, SPARKLINE, IMPORTHTML, IMPORTDATA, GOOGLETRANSLATE, FLATTEN, and COUNTUNIQUE have no direct Excel equivalent.
What is ARRAYFORMULA for?
ARRAYFORMULA runs a single formula across an entire range instead of making you copy it into every row. Write =ARRAYFORMULA(A2:A * B2:B) once in the top cell and every row below fills itself, including new rows you add later. It is the simplest way to stop a sheet from breaking as the data grows, and it is one of the things Excel cannot do.
Can I print this cheat sheet?
Yes. The PDF version is laid out to print cleanly on standard letter paper, so you can pin it next to your monitor or hand it to a teammate. Request it with the form above and it lands in your inbox as a printable file.
Skip the build, start from a finished file
Simple Sheets has 100+ ready-to-use Excel and Google Sheets templates. Budgets, trackers, dashboards, planners, and more.
Browse the template catalogMore free stuff: free calculators and tools and free Excel templates.
