Google Sheets cheat sheet formulas you can adapt with confidence
By Yurii Vasyliev6 min read
On this page
- Starter Google Sheets cheat sheet formulas for everyday jobs
- How to adapt a copied formula to your own sheet
- Build long formulas in layers
- Shortcuts worth learning early
- Checking a formula an AI wrote for you
- Action steps
- Frequently asked questions
- What is the difference between VLOOKUP and XLOOKUP in Google Sheets?
- Why does my formula work in one cell but fail when I fill down?
- Can I use these formulas in Excel?
Searching for a Google Sheets cheat sheet formulas list is easy. Making those formulas work on your own data is harder, because every example points at ranges that are not yours. A pasted formula aimed at the wrong column either errors out or quietly returns a wrong answer. Below you get a short set of formulas to copy, plus the habits that let you adapt them safely.
Starter Google Sheets cheat sheet formulas for everyday jobs
Start with the jobs most people repeat every week: totals, conditional totals, lookups, and text splitting. The table lists one formula for each, with the part you need to change. Watch the column letters, since that is where most copy-paste errors begin. For a bigger library, Exceljet's formula examples cover more than 1,000 tasks, grouped under headings like Count, Sum, and Lookup. They are written for Excel, but many of the same functions work in Sheets. The Corporate Finance Institute's formulas cheat sheet offers a shorter categorized list with syntax.
| Task | Formula | Change this |
|---|---|---|
| Add a column | =SUM(B2:B100) | The range |
| Add if one condition matches | =SUMIF(A2:A100, "West", C2:C100) | Test range, test word, sum range |
| Count with two conditions | =COUNTIFS(F2:F367, ">=240", F2:F367, "<=270") | Range and both limits |
| Look up a value | =XLOOKUP(E2, A2:A100, C2:C100, "Not found") | Lookup cell, search column, return column |
| Older lookup | =VLOOKUP(E2, A2:C100, 3, FALSE) | Table range, column number |
| List unique values | =UNIQUE(A2:A100) | The range |
| Split text | =SPLIT(A2, ",") | The delimiter |
| Label a result | =IF(B2>=100, "High", "Low") | Cell, threshold, labels |
| Multiply two columns | =ARRAYFORMULA(B2:B100*C2:C100) | Both ranges |
Two quirks catch beginners. SPLIT spills its results into the cells to the right, so those cells must stay empty or you get a #REF! error. Also, ARRAYFORMULA returns 0 for blank rows in a multiplication, so limit the range to rows that hold data.
How to adapt a copied formula to your own sheet
Change one piece at a time, and let Sheets show you what it reads. As Ben Collins explains in his list of Google Sheets formula techniques, Sheets colors each range in a formula and outlines the same cells in a matching color. If a colored box covers the wrong cells, fix the range before you press Enter. Put your cursor on a range and press F2 to highlight just that range. Treat every Google Sheets cheat sheet formulas entry as a template, not a finished answer.
Next, check your references. A reference like A2 is relative, which means it shifts when you copy the formula down. A reference like $A$2:$C$100 stays put. Press F4 while your cursor sits in a reference to toggle between the two, which beats typing dollar signs by hand. Lookups show why this matters.
=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)Without the dollar signs, the table range slides down one row for every row you fill. Later lookups then return #N/A because the search column no longer contains their match. If you click into a formula by accident, press Escape to leave without saving changes. The function helper pane also helps, since it highlights the argument you are typing in yellow. If you closed it, click the blue question mark beside the equals sign.
Build long formulas in layers
Add one layer, test it, then add the next. Ben Collins calls this the onion approach: copy the working formula into a new cell before you nest it inside another function. If something breaks, you know which layer caused it. Start with the plain lookup, then wrap it.
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "Not found")Test the inner formula first, because IFERROR hides every error, including the useful ones. A column number larger than your table, for example, would also read "Not found". When a real error does appear, our guide to fixing Google Sheets formula errors covers the common messages. For long formulas, press Ctrl + Enter inside the formula bar to add line breaks, and leave yourself a note with the N function.
=SUM(A1:A100) + N("Sums the first 100 rows of column A")Shortcuts worth learning early
These habits come from the same list of Google Sheets formula techniques, and they save clicks every day.
- F4 toggles a reference between relative and absolute.
- F2 or Shift + Enter opens a formula for editing.
- Ctrl + D (Cmd + D on a Mac) fills the selected range down. Double-clicking the blue square at the cell corner also copies a formula down as far as the neighbouring column has data.
- Select a range and read COUNT, SUM, AVERAGE, MIN, and MAX in the bottom-right corner, with no formula needed.
- Ctrl + Shift + Enter (Cmd + Shift + Enter on a Mac) wraps your formula in ARRAYFORMULA.
Dates trip people up in their own ways, so our simple date formulas for Google Sheets are a good next step once these basics feel familiar.
Checking a formula an AI wrote for you
AI can write a formula from a plain description, but you still have to test it. TechRepublic's walkthrough of using Gemini with Google Sheets shows why. Its author asked for the share of days with a west wind, using values from 0 to 359. The first answer, a COUNTIF formula, threw an error, and a follow-up prompt produced a working COUNTIFS version. The article also notes that two people entering the same prompt may get different responses.
=COUNTIFS(F2:F367, ">=240", F2:F367, "<=270")/COUNT(F2:F367)That formula counts values from 240 to 270 and divides by the count of numbers. Your prompt must name the cells, the limits, and the result you want. Describing the result in plain words is also how Formula Foundry's AI formula generator works, and the same checks apply to its output. Pick three rows where you know the right answer, run the formula, and compare. Even the best cheat sheet for Google Sheets formulas, or an AI answer, deserves that test.
Keep a spare tab with the five formulas you reuse most, each beside a sample row and the answer you expect. The next time a paste misbehaves, you will have a known-good version to compare against.
Action steps
- Name the result: Write the answer you want in one sentence before you choose a formula.
- Copy and edit the ranges: Paste a formula from the table and replace every range with your own.
- Check the colored ranges: Confirm each colored box covers the cells you meant before you press Enter.
- Lock the references: Press F4 on any range that must not shift when you fill down.
- Test on known rows: Compare results against three rows you can calculate by hand, then add IFERROR last.
Frequently asked questions
What is the difference between VLOOKUP and XLOOKUP in Google Sheets?
Both find a value in one column and return a match. VLOOKUP needs a column number counted from the left of your table, plus FALSE for an exact match. XLOOKUP takes separate search and return ranges and lets you set a "not found" message.
Why does my formula work in one cell but fail when I fill down?
Usually a relative reference shifted as you copied it. Press F4 on the range that should stay fixed so it gains dollar signs, then fill down again.
Can I use these formulas in Excel?
SUM, SUMIF, COUNTIFS, IF, and VLOOKUP work in both programs. SPLIT and ARRAYFORMULA are Google Sheets functions, and Excel has TEXTSPLIT for splitting text. Check your Excel version before relying on XLOOKUP or UNIQUE.