50 formulas. Zero fluff.

Google Sheets Formulas Cheat Sheet

This Google Sheets formulas cheat sheet covers the 50+ functions you actually use, VLOOKUP, QUERY, ARRAYFORMULA, IF, dates and text, each with syntax and a real copy-paste example. Free, no signup.

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.

Can't find the formula you need?

Tell us what you're trying to do and we'll build it for you, a single formula or a complete system inside Google Sheets.

See what we build →

Need something more custom?

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

Book a free scoping call

Keep learning

Google Apps Script for Beginners →