Google Sheets Formulas Cheat Sheet: Complete Reference (2025)

Google Sheets shares most of Excel's formula language — but adds a powerful set of Google-exclusive functions: QUERY for SQL-style data analysis, IMPORTRANGE for cross-spreadsheet lookups, ARRAYFORMULA for expanding single formulas across entire columns, and AI-powered helpers like GOOGLETRANSLATE and IMAGE. This cheat sheet covers every category you'll actually use.

Quick reference table

Formula syntax basics

Every formula starts with =. Key syntax rules:

Lock references: Press F4 to cycle A1 → $A$1 → A$1 → $A1.

Separator note: Google Sheets uses , in English locales and ; in many European locales. This guide uses ,.

Lookup functions

VLOOKUP — vertical search

=VLOOKUP(search_key, range, index, [is_sorted])

Example — find price by product name:

=VLOOKUP("Widget", A2:C100, 3, FALSE)

Wrap with IFERROR to suppress #N/A:

=IFERROR(VLOOKUP(A2, Products!$A:$C, 3, FALSE), "Not found")

INDEX + MATCH — flexible two-way lookup

=INDEX(return_range, MATCH(search_key, lookup_range, 0))

Unlike VLOOKUP, the lookup column doesn't have to be first:

=INDEX(C2:C100, MATCH("Widget", A2:A100, 0))

Two-way lookup (row AND column):

=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))

XLOOKUP — modern replacement for VLOOKUP

Available in Google Sheets since 2023:

=XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

match_mode: 0 exact, -1 next smaller, 1 next larger, 2 wildcard.
search_mode: 1 first to last, -1 last to first, 2 binary ascending.

Logical functions

IF

=IF(condition, value_if_true, value_if_false)

=IF(B2>1000, "High", "Low")
=IF(A2="", "Missing", A2)

IFS — multiple conditions without nesting

=IFS(condition1, value1, condition2, value2, ..., TRUE, default)

=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

AND / OR inside IF

=IF(AND(A2>0, B2>0), "Both positive", "Check values")
=IF(OR(A2="Admin", B2="Superuser"), "Access granted", "Denied")

IFERROR / IFNA

=IFERROR(formula, value_if_error)
=IFNA(VLOOKUP(A2,B:C,2,FALSE), "Missing")

Math functions

Statistical functions

Text functions

Extract domain from email:

=MID(A2, FIND("@",A2)+1, LEN(A2))

First name from full name:

=LEFT(A2, FIND(" ",A2)-1)

Date & time functions

Age in years:

=DATEDIF(A2, TODAY(), "Y")

Days until deadline:

=DAYS(B2, TODAY())

ARRAYFORMULA — expand a formula to a whole column

ARRAYFORMULA lets you write one formula that applies to an entire range, avoiding copy-paste and keeping data dynamic.

=ARRAYFORMULA(A2:A100 * B2:B100)

Conditional ARRAYFORMULA (without drag-down):

=ARRAYFORMULA(IF(A2:A100="","", A2:A100 * B2:B100))

Count characters in every row of column A:

=ARRAYFORMULA(LEN(A2:A100))

Join first + last name for all rows:

=ARRAYFORMULA(B2:B100 & " " & C2:C100)

Tip: Wrap with IF(A2:A="",,...) to stop the formula from filling blank rows.

QUERY — SQL-style data analysis

QUERY is one of Google Sheets' most powerful exclusive functions. It uses a subset of SQL syntax called Google Visualization API Query Language.

=QUERY(data, query, [headers])

SELECT + WHERE

=QUERY(A1:D100, "SELECT A, C WHERE B = 'North' AND C > 1000", 1)

ORDER BY + LIMIT

=QUERY(A1:D100, "SELECT A, B, C ORDER BY C DESC LIMIT 10", 1)

GROUP BY + aggregates

=QUERY(A1:D100, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'", 1)

PIVOT table with QUERY

=QUERY(A1:C100, "SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B PIVOT A", 1)

Dynamic criteria with cell references

Inject cell values by closing the string and concatenating:

=QUERY(A1:D100, "SELECT A, C WHERE B = '"&G1&"' AND C > "&H1, 1)

QUERY operators

IMPORTRANGE — pull data from another spreadsheet

=IMPORTRANGE("spreadsheet_url", "range_string")

Example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1abc.../edit", "Sheet1!A1:C100")

• First use prompts you to click Allow access.
• Combine with QUERY to filter the imported data:

=QUERY(IMPORTRANGE("https://...","Sheet1!A1:D100"), "SELECT Col1, Col3 WHERE Col2 > 0", 1)

• Refreshes automatically when the source sheet changes.
• Use named ranges in the source sheet to keep links maintainable.

Google-exclusive functions

GOOGLETRANSLATE

=GOOGLETRANSLATE(text, [source_language], [target_language])

=GOOGLETRANSLATE(A2, "en", "es")       // English → Spanish
=GOOGLETRANSLATE(A2, "auto", "fr")     // auto-detect source

IMAGE — embed an image in a cell

=IMAGE(url, [mode], [height], [width])

=IMAGE("https://example.com/logo.png", 4, 80, 120)

SPARKLINE — tiny chart in a cell

=SPARKLINE(data, {options})

=SPARKLINE(B2:M2)                                          // line chart
=SPARKLINE(B2:M2, {"charttype","bar"; "max",100})          // bar chart
=SPARKLINE(B2:M2, {"charttype","column"; "color","blue"})  // column

HYPERLINK

=HYPERLINK("https://example.com", "Click here")

MAP / REDUCE (Sheets 2023+)

=MAP(A2:A10, LAMBDA(x, x*2))            // multiply every cell by 2
=REDUCE(0, A2:A10, LAMBDA(acc, x, acc+x))  // sum via LAMBDA

Array functions (modern)

FILTER with multiple conditions:

=FILTER(A2:C100, (B2:B100="North") * (C2:C100 > 1000))

Named ranges & named functions

Create a named range: Select cells → Data → Named ranges → Define name.

=SUM(Revenue)          // instead of =SUM(B2:B1000)
=SUMIF(Region, "North", Revenue)

Named functions (custom formulas): Data → Named functions → New function.

// Define: YEAROVER(current, previous) = (current - previous) / previous
=YEAROVER(C2, B2)       // reusable across the sheet

Conditional formatting with formulas

Use Format → Conditional formatting → Custom formula is to colour rows based on formula logic:

Data validation with formulas

Format → Data validation → Custom formula:

Common mistakes

Excel vs Google Sheets: key differences

Google Apps Script: go beyond formulas

For automation that formulas can't handle, use Extensions → Apps Script (JavaScript):

function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sales");
  const total = sheet.getRange("C2:C100").getValues()
    .flat()
    .reduce((sum, v) => sum + (Number(v) || 0), 0);

  MailApp.sendEmail({
    to: "manager@example.com",
    subject: "Weekly Sales Report",
    body: `Total sales this week: $${total.toLocaleString()}`
  });
}

Trigger options: time-driven (hourly/daily/weekly), on-open, on-edit, on-form-submit.

FAQ

Q: What is the difference between Google Sheets and Excel formulas?
Most functions are identical. Google Sheets adds QUERY, IMPORTRANGE, GOOGLETRANSLATE, IMAGE, SPARKLINE, and always-dynamic array functions (FILTER, SORT, UNIQUE). Excel has more financial functions and full VBA support.

Q: How do I reference another sheet in Google Sheets?
Use SheetName!CellRef, for example =Sheet2!A1 or =SUM(Sheet2!B2:B100). For another spreadsheet entirely, use IMPORTRANGE.

Q: Why does my QUERY formula return a #VALUE! error?
Usually caused by mixed data types in a column (e.g. some cells are text, some numbers). Try converting the column with =VALUE() or format all cells consistently before querying.

Q: How do I make a drop-down list in Google Sheets?
Select cells → Data → Data validation → Dropdown (from a range) or Dropdown (from a list). You can also use a UNIQUE formula range as the source to auto-update the list.

Q: What's the difference between ARRAYFORMULA and FILTER?
ARRAYFORMULA expands a single formula across a range (like drag-fill but dynamic). FILTER returns a subset of rows matching a condition — it outputs multiple rows automatically without ARRAYFORMULA.

Q: How do I use QUERY to combine data from multiple sheets?
Use VSTACK to stack the ranges first, then QUERY the combined data:

=QUERY(VSTACK(Sheet1!A1:C100, Sheet2!A1:C100), "SELECT Col1, SUM(Col3) GROUP BY Col1", 1)