50 formulas. Zero fluff.

The Google Sheets Formula Cheat Sheet

Every formula you actually need, with syntax and real examples. Copy, paste, done.

01Lookups5 formulas 02Text8 formulas 03Dates6 formulas 04Logic5 formulas 05Aggregation6 formulas 06Arrays6 formulas 07Data Import4 formulas 08Power Formulas5 formulas

Lookups and References

VLOOKUP

Find a value in the first column and return from another column.

=VLOOKUP(search_key, range, index, FALSE)

Example

=VLOOKUP(A2, Products!A:C, 3, FALSE)

Returns the price for a product.

INDEX + MATCH

More flexible than VLOOKUP. Works in any direction.

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

Example

=INDEX(B:B, MATCH("Widget", A:A, 0))

Finds Widget's row and returns column B.

XLOOKUP

Modern replacement for VLOOKUP with simpler syntax.

=XLOOKUP(search_key, lookup_range, result_range)

Example

=XLOOKUP(D2, A:A, C:C)

Looks up D2 in column A, returns column C.

INDIRECT

Convert a text string into a cell reference.

=INDIRECT(ref_string)

Example

=INDIRECT("Sheet2!A"&B1)

Dynamically references a cell on another sheet.

OFFSET

Returns a range shifted from a starting point.

=OFFSET(reference, rows, cols, [height], [width])

Example

=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))

Sums a dynamic range.

Text

LEFT

Extract characters from the start.

=LEFT(text, num_chars)

Example

=LEFT(A2, 3)

Returns the first 3 characters.

RIGHT

Extract characters from the end.

=RIGHT(text, num_chars)

Example

=RIGHT(A2, 4)

Returns the last 4 characters.

MID

Extract characters from the middle.

=MID(text, start, length)

Example

=MID(A2, 5, 3)

Extracts 3 characters starting at position 5.

TRIM

Remove extra spaces.

=TRIM(text)

Example

=TRIM(" hello world ")

Returns "hello world".

SUBSTITUTE

Replace specific text.

=SUBSTITUTE(text, old, new)

Example

=SUBSTITUTE(A2, "-", "/")

Replaces dashes with slashes.

SPLIT

Split text into columns.

=SPLIT(text, delimiter)

Example

=SPLIT("John,Doe,CEO", ",")

Splits into 3 cells.

TEXTJOIN

Join text with a separator.

=TEXTJOIN(delimiter, ignore_empty, range)

Example

=TEXTJOIN(", ", TRUE, A1:A5)

Joins values with commas.

REGEXEXTRACT

Extract text matching a pattern.

=REGEXEXTRACT(text, regex)

Example

=REGEXEXTRACT(A2, "[0-9]+")

Pulls the first number from a string.

Dates and Time

TODAY

Current date, updates automatically.

=TODAY()

Example

=TODAY()-A2

Gives days since a date.

DATEDIF

Difference between dates.

=DATEDIF(start, end, unit)

Example

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

Returns months elapsed.

EOMONTH

Last day of a month offset.

=EOMONTH(start_date, months)

Example

=EOMONTH(TODAY(), 0)

Returns the last day of this month.

WORKDAY

Add business days.

=WORKDAY(start_date, days, [holidays])

Example

=WORKDAY(A2, 10)

Adds 10 business days.

TEXT (dates)

Format a date as text.

=TEXT(date, format)

Example

=TEXT(A2, "MMM YYYY")

Returns "Mar 2026".

WEEKDAY

Day of the week as a number.

=WEEKDAY(date, [type])

Example

=WEEKDAY(TODAY(), 2)

Returns 1 for Monday through 7 for Sunday.

Logic

Basic conditional.

=IF(condition, value_if_true, value_if_false)

Example

=IF(A2>100, "Over budget", "OK")

Returns "Over budget" if A2 exceeds 100.

IFS

Multiple conditions without nesting.

=IFS(cond1, val1, cond2, val2, ...)

Example

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

Assigns letter grades based on score.

AND / OR

Combine conditions.

=AND(cond1, cond2) / =OR(cond1, cond2)

Example

=IF(AND(A2>0, B2="Paid"), "Complete", "Pending")

Returns "Complete" only when both conditions are met.

IFERROR

Catch errors gracefully.

=IFERROR(value, error_value)

Example

=IFERROR(VLOOKUP(A2, data, 2, 0), "Not found")

Returns "Not found" instead of an error.

SWITCH

Match against multiple values.

=SWITCH(expression, case1, val1, case2, val2, default)

Example

=SWITCH(A2, "US", "Dollar", "UK", "Pound", "Unknown")

Returns the currency name for each country code.

Aggregation

SUMIF

Sum with one condition.

=SUMIF(range, criteria, sum_range)

Example

=SUMIF(B:B, "Paid", C:C)

Sums amounts where status is "Paid".

SUMIFS

Sum with multiple conditions.

=SUMIFS(sum_range, range1, criteria1, range2, criteria2)

Example

=SUMIFS(D:D, B:B, "Sales", C:C, ">1000")

Sums column D where department is "Sales" and value exceeds 1000.

COUNTIF

Count cells matching a condition.

=COUNTIF(range, criteria)

Example

=COUNTIF(A:A, "Complete")

Counts completed items.

COUNTIFS

Count with multiple conditions.

=COUNTIFS(range1, criteria1, range2, criteria2)

Example

=COUNTIFS(A:A, "Open", B:B, ">30")

Counts open items where column B exceeds 30.

AVERAGEIF

Average with condition.

=AVERAGEIF(range, criteria, average_range)

Example

=AVERAGEIF(A:A, "Q1", B:B)

Averages Q1 values.

SUMPRODUCT

Multiply arrays then sum.

=SUMPRODUCT(array1, array2)

Example

=SUMPRODUCT(B2:B10, C2:C10)

Calculates total revenue from qty x price.

Arrays

ARRAYFORMULA

Apply a formula to an entire column.

=ARRAYFORMULA(expression)

Example

=ARRAYFORMULA(B2:B*C2:C)

Multiplies every row at once.

FILTER

Return rows matching conditions.

=FILTER(range, condition1, [condition2])

Example

=FILTER(A:C, B:B="Active")

Shows only active rows.

SORT

Sort a range.

=SORT(range, sort_column, ascending)

Example

=SORT(A2:D, 3, FALSE)

Sorts by column 3 descending.

UNIQUE

Remove duplicates.

=UNIQUE(range)

Example

=UNIQUE(A2:A)

Returns each value once.

FLATTEN

Collapse a 2D range into one column.

=FLATTEN(range)

Example

=FLATTEN(A1:C3)

Turns 9 cells into a single column.

TRANSPOSE

Flip rows and columns.

=TRANSPOSE(range)

Example

=TRANSPOSE(A1:D1)

Turns a row into a column.

Data Import

IMPORTRANGE

Pull data from another spreadsheet.

=IMPORTRANGE(url, range_string)

Example

=IMPORTRANGE("https://docs.google.com/...", "Sheet1!A:D")

Imports columns A through D from another spreadsheet.

IMPORTDATA

Import CSV/TSV from a URL.

=IMPORTDATA(url)

Example

=IMPORTDATA("https://example.com/data.csv")

Pulls in a CSV file directly into the sheet.

IMPORTHTML

Import tables or lists from a webpage.

=IMPORTHTML(url, query, index)

Example

=IMPORTHTML("https://...", "table", 1)

Imports the first table found on the page.

IMAGE

Display an image in a cell.

=IMAGE(url, [mode])

Example

=IMAGE("https://example.com/logo.png", 1)

Fits image to cell.

Power Formulas

QUERY

Run SQL-like queries on your data.

=QUERY(data, query, [headers])

Example

=QUERY(A:E, "SELECT A, SUM(D) WHERE B='Sales' GROUP BY A")

Groups and sums sales data by name.

LAMBDA

Create reusable custom functions.

=LAMBDA(param, expression)(value)

Example

=LAMBDA(x, x*1.1)(A2)

Adds 10% markup.

MAP

Apply a function to every element.

=MAP(array, LAMBDA(val, expression))

Example

=MAP(A2:A, LAMBDA(v, PROPER(TRIM(v))))

Cleans and title-cases names.

REDUCE

Accumulate values.

=REDUCE(initial, array, LAMBDA(acc, val, expression))

Example

=REDUCE(0, A2:A, LAMBDA(a, v, a+v))

Running total.

LET

Name intermediate values for readability.

=LET(name, value, expression)

Example

=LET(total, SUM(A:A), avg, AVERAGE(A:A), total/avg)

Names intermediate calculations for clarity.

Formula AI

Can't find your formula?

Describe what you need in plain English and our AI will write the exact formula for you.

Need something more custom?

We build dashboards, CRMs, and complete business systems inside Google Sheets. No subscriptions. No vendor lock-in.

Book a free scoping call