Google Sheets Formulas Cheat Sheet
The complete list of Google Sheets formulas for real tasks — copy any one, paste it in, done. Grouped by what you're trying to do.
Lookup & reference
| Task | Formula | |
|---|---|---|
| Look up a value in a column (VLOOKUP) | =VLOOKUP(A2, Sheet2!A:C, 3, FALSE) |
|
| Flexible lookup, any direction (XLOOKUP) | =XLOOKUP(E2, A2:A100, B2:B100) |
|
| Left-lookup / robust lookup (INDEX + MATCH) | =INDEX(B2:B100, MATCH(E2, A2:A100, 0)) |
|
| Find a value's position (MATCH) | =MATCH("apple", A2:A, 0) |
|
| Return rows that meet a condition (FILTER) | =FILTER(A2:C, B2:B>100) |
|
| SQL-style queries on a range (QUERY) | =QUERY(A1:D100, "SELECT A, B WHERE C > 100", 1) |
|
| Sort a range by a column (SORT) | =SORT(A2:C, 2, FALSE) |
|
| List distinct values (UNIQUE) | =UNIQUE(A2:A) |
Count, sum & math
| Task | Formula | |
|---|---|---|
| Count cells that meet one condition (COUNTIF) | =COUNTIF(A2:A100, ">100") |
|
| Count with multiple conditions (COUNTIFS) | =COUNTIFS(A2:A100, "Paid", B2:B100, ">100") |
|
| Sum cells that meet one condition (SUMIF) | =SUMIF(B2:B100, ">100", C2:C100) |
|
| Sum with multiple conditions (SUMIFS) | =SUMIFS(C2:C100, A2:A100, "West", B2:B100, "Paid") |
|
| Add up a range (SUM) | =SUM(A2:A100) |
|
| Average a range (AVERAGE / AVERAGEIF) | =AVERAGE(A2:A100) |
|
| Multiply two cells | =A2*B2 |
|
| Subtract one cell from another | =A2-B2 |
|
| Work out a percentage | =A2/B2 |
|
| Round a number to N decimals (ROUND) | =ROUND(A2, 2) |
Text & cleanup
| Task | Formula | |
|---|---|---|
| Join text with a space (&) | =A2&" "&B2 |
|
| Join a range with a delimiter (TEXTJOIN) | =TEXTJOIN(", ", TRUE, A2:A10) |
|
| Grab part of a string (LEFT/RIGHT/MID) | =LEFT(A2, 3) |
|
| Split text into columns (SPLIT) | =SPLIT(A2, ",") |
|
| Replace text (SUBSTITUTE) | =SUBSTITUTE(A2, "-", "") |
|
| Format numbers/dates as text (TEXT) | =TEXT(A2, "$#,##0.00") |
|
| Extract with a pattern (REGEXEXTRACT) | =REGEXEXTRACT(A2, "[0-9]+") |
|
| Test a pattern (REGEXMATCH) | =REGEXMATCH(A2, "[0-9]") |
|
| Find & remove duplicates | =COUNTIF(A$2:A2, A2)>1 |
Logic, dates & arrays
| Task | Formula | |
|---|---|---|
| One condition, two outcomes (IF) | =IF(A2>100, "High", "Low") |
|
| Multiple conditions (IFS) | =IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", TRUE,"F") |
|
| Catch errors cleanly (IFERROR) | =IFERROR(A2/B2, 0) |
|
| Build / get a date (DATE, TODAY) | =DATE(2026, 9, 29) |
|
| Difference between two dates (DATEDIF) | =DATEDIF(A2, B2, "Y") |
|
| Apply a formula to a whole column (ARRAYFORMULA) | =ARRAYFORMULA(A2:A100 * B2:B100) |
|
| Pull data from another file (IMPORTRANGE) | =IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit", "Sheet1!A1:D100") |
This is the whole Google Sheets formula toolkit on one page. Each row gives you the ready-to-paste formula for a real job — hit Copy, paste it into your sheet, then swap the ranges for your own. Want the full walkthrough, variations and examples for any formula? Click its name to open the detailed page.
Every formula here is tested in Google Sheets and written for the task, not the keyword: lookups, conditional counts and sums, text cleanup, logic, dates and array formulas.
FAQ
How do I use these formulas?
Click Copy on any row, paste it into a cell in Google Sheets, then change the cell references (like A2:A100) to match where your data actually is.
Do these work in Excel too?
Most core formulas (SUM, IF, VLOOKUP, XLOOKUP, INDEX/MATCH) are identical. Google-specific ones like QUERY, FILTER, IMPORTRANGE and REGEXEXTRACT are Sheets-only or behave differently in Excel.
Why does my formula return an error?
The most common causes are a wrong range, a comma vs semicolon separator, or looking up a value that doesn't exist. Wrap the formula in IFERROR to show a clean fallback while you debug.