Google Sheets Formulas Cheat Sheet (Free, Copy-Paste Ready)
Spreadsheet formulas are one of the highest-return skills you can pick up: a few minutes learning the right function can replace hours of manual copying, counting, and cleaning. This Google Sheets formulas cheat sheet collects the formulas you will actually reach for, grouped by job, with a short "what it does" note and a copy-paste example for each.
Everything here works in the free version of Google Sheets with a normal Google account. No Workspace subscription, no add-ons, no paid plan. Bookmark this page, keep it open in a tab while you build, and the patterns will stick fast.
A quick note on syntax: Google Sheets uses commas to separate arguments in the examples below. If your sheet is set to a locale that uses commas as decimal separators (common in parts of Europe and Latin America), you may need to use semicolons instead, for example =SUM(A1; A2).
Basic math and counting
These are the workhorses. If you only memorize one section, make it this one.
=SUM(A2:A100) // add up a range
=AVERAGE(A2:A100) // mean of a range
=MIN(A2:A100) // smallest value
=MAX(A2:A100) // largest value
=COUNT(A2:A100) // count cells that contain numbers
=COUNTA(A2:A100) // count cells that are not empty (text or numbers)
=COUNTBLANK(A2:A100) // count empty cells
=ROUND(A2, 2) // round to 2 decimal places
=ABS(A2) // absolute value (strips the minus sign)
=A2 / B2 // simple division
=PRODUCT(A2:A10) // multiply a whole range together
Worked example. You have prices in B2:B20 and quantities in C2:C20. To get a grand total without a helper column:
=SUMPRODUCT(B2:B20, C2:C20)
SUMPRODUCT multiplies each price by its quantity and adds the results in one step. It is the single most underused formula on this list.
Conditional math: SUMIF, COUNTIF, AVERAGEIF
This is where spreadsheets start to feel like a database. You filter and aggregate in one formula.
=COUNTIF(A2:A100, "Paid") // count rows where the value is "Paid"
=COUNTIF(B2:B100, ">100") // count rows greater than 100
=SUMIF(A2:A100, "Marketing", C2:C100) // sum column C where column A = "Marketing"
=AVERAGEIF(B2:B100, ">0") // average of positive numbers only
When you need to match on more than one condition, switch to the plural versions:
=COUNTIFS(A2:A100, "Paid", B2:B100, ">100")
=SUMIFS(C2:C100, A2:A100, "Marketing", B2:B100, "<>0")
Read SUMIFS as: sum column C, where column A equals "Marketing" and column B is not zero. The order is different from SUMIF (the sum range comes first), so it trips people up. Keep this cheat sheet handy until it feels natural.
Lookups: VLOOKUP, INDEX/MATCH, and XLOOKUP
Lookups pull a value from another table based on a key, like finding a price from a product code. This is the most-searched formula category, so it gets the most detail.
VLOOKUP
=VLOOKUP("SKU-204", A2:D100, 3, FALSE)
What it does: search column A (the first column of the range) for "SKU-204", and return the value from the 3rd column of that range. The final FALSE means "exact match only" — almost always what you want.
The catch: VLOOKUP can only look to the right of the key column, and it breaks if you insert a column in the middle of the range. That is why many people have moved on to the next two options.
INDEX/MATCH
=INDEX(C2:C100, MATCH("SKU-204", A2:A100, 0))
MATCH finds the row number where "SKU-204" lives in A2:A100 (the 0 means exact match). INDEX then returns the value at that row from C2:C100. Because the lookup column and the result column are independent, this searches in any direction and does not break when columns shift.
XLOOKUP
If your sheet supports it, XLOOKUP is the cleanest of the three:
=XLOOKUP("SKU-204", A2:A100, C2:C100, "Not found")
Read it as: look up "SKU-204" in A2:A100, return the matching value from C2:C100, and if there is no match, show "Not found" instead of an error.
Text formulas
For cleaning up names, codes, and imported data, these are essential.
=CONCATENATE(A2, " ", B2) // join first and last name with a space
=A2 & " " & B2 // the same join, shorter syntax
=UPPER(A2) // ALL CAPS
=LOWER(A2) // all lowercase
=PROPER(A2) // Title Case Each Word
=TRIM(A2) // remove extra/leading/trailing spaces
=LEN(A2) // number of characters
=LEFT(A2, 3) // first 3 characters
=RIGHT(A2, 4) // last 4 characters
=MID(A2, 2, 5) // 5 characters starting at position 2
=SUBSTITUTE(A2, "-", "") // remove all dashes
=SPLIT(A2, ",") // split "a,b,c" into separate columns
Worked example. You imported a column of emails and want just the domain (the part after the @):
=RIGHT(A2, LEN(A2) - FIND("@", A2))
FIND locates the @, LEN gives the total length, and RIGHT keeps everything after the @. Combining small text functions like this solves most "clean my data" problems.
Date and time formulas
Dates are stored as numbers under the hood, so you can do arithmetic on them directly.
=TODAY() // today's date, updates daily
=NOW() // current date and time
=YEAR(A2) // pull the year out of a date
=MONTH(A2) // pull the month (1-12)
=DAY(A2) // pull the day of the month
=A2 - B2 // number of days between two dates
=EDATE(A2, 3) // the date 3 months after A2
=EOMONTH(A2, 0) // last day of A2's month
=WEEKDAY(A2) // day of week as a number (1 = Sunday)
=DATEDIF(A2, B2, "Y") // whole years between two dates (great for age)
Worked example. To flag invoices older than 30 days, put this next to each due date in A2:
=IF(TODAY() - A2 > 30, "Overdue", "OK")
Conditional logic: IF, IFS, and IFERROR
IF is the gateway to "smart" spreadsheets that label, score, and route data for you.
=IF(B2 >= 50, "Pass", "Fail")
=IF(AND(B2 >= 50, C2 = "Submitted"), "Pass", "Fail")
=IF(OR(B2 >= 90, C2 = "Bonus"), "Top", "Standard")
When you have several tiers, nesting IFs gets ugly. Use IFS instead — it reads top to bottom and returns the first match:
=IFS(B2 >= 90, "A", B2 >= 80, "B", B2 >= 70, "C", TRUE, "F")
The final TRUE is the catch-all "otherwise" case. And to keep errors from cluttering your sheet, wrap any formula that might fail:
=IFERROR(VLOOKUP("SKU-204", A2:D100, 3, FALSE), "Not found")
If the lookup fails, you see "Not found" instead of an ugly #N/A.
Array formulas and the modern toolkit
Array formulas apply one formula to a whole range at once, so you do not have to copy it down row by row. They keep your sheet clean and update automatically as new rows arrive.
=ARRAYFORMULA(A2:A100 * B2:B100) // multiply every matching row in one formula
=ARRAYFORMULA(IF(A2:A100 = "", "", A2:A100 & " " & B2:B100))
The newer dynamic array functions are worth learning too:
=FILTER(A2:C100, B2:B100 > 100) // return only the rows where column B > 100
=SORT(A2:C100, 2, FALSE) // sort the range by the 2nd column, descending
=UNIQUE(A2:A100) // list of distinct values, duplicates removed
=QUERY(A1:D100, "select A, B where C > 100 order by B desc")
QUERY deserves special mention: it lets you write a mini SQL-style statement against your sheet. If you already know a little SQL, it is the most powerful single function in Google Sheets — and if you do not, it is a gentle on-ramp to learning it.
Quick error reference
When a formula misbehaves, the error message tells you what went wrong:
#N/A— a lookup found no match. Wrap it inIFERROR.#REF!— the formula points at a cell or range that was deleted. Check for removed columns.#DIV/0!— you divided by zero or an empty cell. Guard it:=IFERROR(A2 / B2, 0).#VALUE!— you mixed text and numbers, for example trying to add"abc"to a number.#NAME?— a typo in the function name, or a missing quote around text.
Let AI write the formula for you
Memorizing syntax is optional now. You can describe what you want in plain English and let AI generate the exact formula, including the tricky SUMIFS argument order. Our free micro-course AI for Google Sheets & Docs (No Code) shows you how to turn requests like "sum sales for the Marketing team in Q2" into working formulas, clean up messy data, and automate repetitive tasks — all without writing code.
If you would rather work inside the tools you already use, our guide on how to use Gemini in Google Docs and Sheets walks through using Google's built-in AI to generate and explain formulas on the spot. Pair either approach with this cheat sheet: AI gets you the formula fast, and the reference here helps you understand and tweak what it gives you.
Key takeaways
- Master the basic math and conditional sections (
SUM,SUMIFS,COUNTIFS) first — they cover the majority of everyday spreadsheet work. - For lookups, prefer INDEX/MATCH or XLOOKUP over
VLOOKUP; they search in any direction and survive column changes. - Combine small text functions (
LEFT,RIGHT,FIND,TRIM) to clean almost any imported data. - Reach for ARRAYFORMULA, FILTER, UNIQUE, and QUERY to handle whole ranges in a single, self-updating formula.
- Wrap risky formulas in IFERROR so a missing match never breaks your sheet.
Want to go from copying formulas to genuinely understanding your data? Start the free AI for Google Sheets & Docs (No Code) micro-course on FreeAcademy.ai — it is short, hands-on, and comes with a free certificate you can add to your resume or LinkedIn.