Complete Guide to Google Sheets Formulas

How Google Sheets Formulas Work

Every formula in Google Sheets starts with an equals sign (=). After that, you either write a mathematical expression like =A1+B1 or call a function like =SUM(A1:A10).

Google Sheets evaluates your formula and displays the result in the cell. The formula itself stays visible in the formula bar when you select the cell.

Formula vs. Function

A formula is any calculation that starts with =. A function is a named, pre-built formula. =A1*1.21 is a formula. =ROUND(A1*1.21, 2) is a formula that uses the ROUND function.

Cell References

Formulas become powerful when they reference other cells instead of hardcoded values.

Press F4 while editing a cell reference to cycle through these modes.

Referencing Other Sheets

To reference a cell on another sheet within the same spreadsheet, use the sheet name followed by an exclamation mark:

=Sheet2!A1
='Sales Data'!B5:B100

If the sheet name contains spaces, wrap it in single quotes.

Math & Aggregate Formulas

These are the building blocks of any spreadsheet. If you're new to Google Sheets, start here.

SUM

Adds values in a range.

=SUM(A1:A100)
=SUM(A1, B1, C1)
=SUM(A:A)           // entire column

AVERAGE, MIN, MAX

AVERAGE calculates the arithmetic mean. MIN and MAX return the smallest and largest values.

=AVERAGE(B2:B50)    // mean of range
=MIN(B2:B50)        // smallest value
=MAX(B2:B50)        // largest value

COUNT & COUNTA

COUNT counts cells containing numbers. COUNTA counts all non-empty cells, including text.

=COUNT(A1:A100)     // only numbers
=COUNTA(A1:A100)    // any non-empty cell

ROUND, ROUNDUP, ROUNDDOWN

Control decimal precision in your calculations.

=ROUND(3.14159, 2)      // 3.14
=ROUNDUP(3.141, 1)      // 3.2
=ROUNDDOWN(3.149, 1)    // 3.1

Conditional & Logical Formulas

Conditional formulas let you make decisions inside cells. They evaluate a condition and return different results depending on whether it's true or false.

IF

The fundamental conditional. Returns one value if a condition is true, another if false.

=IF(A1>100, "Over budget", "OK")
=IF(B2="", "Missing", B2)

IFS

Checks multiple conditions in sequence. Cleaner than nested IF statements.

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")

AND, OR, NOT

Combine or negate conditions inside IF.

=IF(AND(A1>0, B1>0), "Both positive", "No")
=IF(OR(A1="Yes", A1="Y"), TRUE, FALSE)
=IF(NOT(ISBLANK(A1)), "Has value", "Empty")

SWITCH

Matches a value against a list of cases. Much cleaner than nested IFs for exact matches.

=SWITCH(A1, "US", "United States", "UK", "United Kingdom", "DE", "Germany", "Unknown")

IFERROR & IFNA

Catch errors and return a fallback value. Essential for building robust sheets.

=IFERROR(VLOOKUP(A1, data, 2, FALSE), "Not found")
=IFNA(XLOOKUP(A1, B:B, C:C), 0)

SUMIF, COUNTIF & Conditional Aggregates

These formulas aggregate data based on criteria — the most practical formulas for real-world reporting.

SUMIF & SUMIFS

SUMIF sums cells that match a single criterion. SUMIFS supports multiple criteria.

=SUMIF(A:A, "Product X", B:B)
=SUMIFS(C:C, A:A, "US", B:B, ">100")

The second example sums column C where column A is "US" and column B is greater than 100.

COUNTIF & COUNTIFS

Count cells matching criteria.

=COUNTIF(A:A, "Completed")
=COUNTIF(B:B, "<>"&"")          // count non-empty
=COUNTIFS(A:A, "US", B:B, ">0") // multiple criteria

AVERAGEIF & AVERAGEIFS

Calculate the average of cells matching criteria.

=AVERAGEIF(A:A, "Sales", B:B)
=AVERAGEIFS(C:C, A:A, "Q1", B:B, "Revenue")

MINIFS & MAXIFS

Find the minimum or maximum value that meets conditions.

=MINIFS(B:B, A:A, "Product A")
=MAXIFS(B:B, A:A, "2024")

Lookup & Reference Formulas

Lookup formulas find and retrieve data from your spreadsheet. If you work with any structured data, you'll use these daily.

VLOOKUP

Searches the first column of a range for a key and returns a value from a specified column.

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

This searches for the value in A2 within the Products sheet, and returns the value from the 3rd column. The FALSE parameter means exact match.

Limitations: VLOOKUP only searches the first column of your range and can only look to the right. For more flexibility, use INDEX/MATCH or XLOOKUP.

XLOOKUP

The modern replacement for VLOOKUP. Searches in any direction, supports exact and approximate matching, and has built-in error handling.

=XLOOKUP(A2, Products!B:B, Products!D:D, "Not found")

INDEX & MATCH

The classic alternative to VLOOKUP. More flexible because you can look up values in any direction.

=INDEX(C:C, MATCH(A2, B:B, 0))

MATCH finds the row position of A2 within column B. INDEX returns the value at that position from column C.

IMPORTRANGE

Pull data from a completely different Google Sheets file.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:D100")

The first time you use IMPORTRANGE, Google Sheets will ask you to grant access between the two files.

Text Formulas

Text formulas help you clean, extract, combine, and transform strings. Essential for working with imported data, names, URLs, or any unstructured text.

CONCATENATE, TEXTJOIN & the & Operator

Combine text from multiple cells.

=A1 & " " & B1
=CONCATENATE(A1, " ", B1)
=TEXTJOIN(", ", TRUE, A1:A10)    // joins with comma, skips empty

TEXTJOIN is the most flexible — you set the delimiter and can skip blank cells.

LEFT, RIGHT, MID

Extract parts of a string by position.

=LEFT(A1, 3)           // first 3 characters
=RIGHT(A1, 4)          // last 4 characters
=MID(A1, 5, 3)         // 3 characters starting at position 5

TRIM, CLEAN & SUBSTITUTE

Clean up messy data.

=TRIM(A1)                              // remove extra spaces
=CLEAN(A1)                             // remove non-printable characters
=SUBSTITUTE(A1, "old", "new")          // replace text
=SUBSTITUTE(A1, " ", "")              // remove all spaces

SPLIT

Split a string into separate cells by a delimiter.

=SPLIT(A1, ",")        // split by comma
=SPLIT(A1, " ")        // split by space

LEN & FIND

LEN returns the length of a string. FIND locates a substring within text.

=LEN(A1)               // character count
=FIND("@", A1)         // position of @ in email

REGEXEXTRACT, REGEXMATCH & REGEXREPLACE

Google Sheets has built-in regex support — a feature Excel lacks.

=REGEXEXTRACT(A1, "[0-9]+")           // extract first number
=REGEXMATCH(A1, "^https")             // check if starts with https
=REGEXREPLACE(A1, "[^a-zA-Z]", "")   // keep only letters

Date & Time Formulas

Google Sheets stores dates as numbers internally (days since December 30, 1899). These functions let you work with dates in human-readable ways.

TODAY & NOW

=TODAY()               // current date (updates daily)
=NOW()                 // current date and time

DATE, YEAR, MONTH, DAY

=DATE(2024, 12, 25)    // create a date
=YEAR(A1)              // extract year
=MONTH(A1)             // extract month (1-12)
=DAY(A1)               // extract day

DATEDIF

Calculate the difference between two dates in years, months, or days.

=DATEDIF(A1, B1, "D")   // days between
=DATEDIF(A1, B1, "M")   // months between
=DATEDIF(A1, B1, "Y")   // years between

WEEKDAY & WORKDAY

=WEEKDAY(A1)                    // day of week (1=Sunday)
=WORKDAY(A1, 10)                // date 10 business days later
=NETWORKDAYS(A1, B1)            // business days between two dates

Array & Dynamic Formulas

Array formulas process entire ranges at once instead of cell by cell. They're what separate basic spreadsheet users from power users.

ARRAYFORMULA

Apply a formula to an entire column with a single cell entry.

=ARRAYFORMULA(A2:A * B2:B)

This multiplies every row in column A by the corresponding row in column B. No dragging needed.

Common use: combine with IF to avoid processing empty rows:

=ARRAYFORMULA(IF(A2:A<>"", A2:A * B2:B, ""))

FILTER

Return only rows that match a condition. This is a dynamic array — the results spill into multiple cells automatically.

=FILTER(A2:D100, B2:B100="Active")
=FILTER(A2:C, B2:B>100, C2:C="US")

SORT

Sort a range dynamically.

=SORT(A2:D100, 3, FALSE)    // sort by column 3, descending

UNIQUE

Return deduplicated values from a range.

=UNIQUE(A2:A100)
=SORT(UNIQUE(A2:A100))       // sorted unique values

TRANSPOSE

Flip rows to columns or columns to rows.

=TRANSPOSE(A1:D1)            // row to column
=TRANSPOSE(A1:A10)           // column to row

The QUERY Function

QUERY is one of Google Sheets' most powerful unique features. It lets you run SQL-like queries on your data.

=QUERY(A1:E100, "SELECT A, C, SUM(E) WHERE B='Active' GROUP BY A, C ORDER BY SUM(E) DESC")

Basic QUERY Syntax

QUERY is especially powerful when combined with IMPORTRANGE — you can query data from other spreadsheets without importing the entire dataset.

Importing Web Data

Google Sheets can pull data directly from the web — no APIs or coding required.

IMPORTHTML

Import tables or lists from any public web page.

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

The third argument specifies which table or list on the page to import (1 = first).

IMPORTXML

Extract structured data from web pages using XPath selectors.

=IMPORTXML("https://example.com", "//h1")
=IMPORTXML("https://example.com", "//meta[@name='description']/@content")

IMPORTDATA

Import CSV or TSV data from a URL.

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

GOOGLEFINANCE

Pull live and historical stock data directly into your spreadsheet.

=GOOGLEFINANCE("GOOG", "price")
=GOOGLEFINANCE("GOOG", "close", DATE(2024,1,1), DATE(2024,12,31), "WEEKLY")

Formula Errors & Troubleshooting

Every Google Sheets user encounters formula errors. Here's what each one means and how to fix it.

Pro tip: wrap any formula in IFERROR() to display a clean fallback instead of an error:

=IFERROR(your_formula, "—")

Essential Keyboard Shortcuts

Speed up your formula work with these shortcuts.

Best Practices for Writing Formulas

Use Named Ranges

Instead of =SUM(B2:B500), define a named range called revenue and write =SUM(revenue). Go to Data > Named ranges to set them up. This makes formulas readable and easier to maintain.

Break Complex Formulas Into Steps

If your formula spans 200 characters, split it into helper columns. A chain of simple formulas is easier to debug than one massive nested formula.

Minimize Volatile Functions

NOW(), TODAY(), RAND(), and INDIRECT() recalculate every time the sheet changes. Use them sparingly in large spreadsheets to avoid slowdowns.

Use ARRAYFORMULA Instead of Dragging

A single ARRAYFORMULA in row 2 that covers the entire column is faster, cleaner, and less error-prone than dragging a formula down thousands of rows.

Quick Reference Cheat Sheet

Frequently Asked Questions About Google Sheets Formulas

What are the most used formulas in Google Sheets?

The most commonly used formulas are SUM, AVERAGE, COUNT, IF, VLOOKUP, CONCATENATE, and FILTER. These cover the core needs of most spreadsheet work: adding numbers, checking conditions, looking up data, and combining text.

How do I start a formula in Google Sheets?

Every formula starts with an equals sign (=). Click on a cell, type = followed by your function name and arguments. For example, =SUM(A1:A10) adds all values in cells A1 through A10.

What is the difference between a formula and a function?

A formula is any expression that begins with = and performs a calculation, like =A1+B1. A function is a pre-built formula with a name, like =SUM(A1:A10). All functions are formulas, but not all formulas use functions.

Can Google Sheets formulas reference other spreadsheets?

Yes. Use IMPORTRANGE to pull data from other Google Sheets files. The syntax is =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:B10"). You'll need to grant access the first time you connect two spreadsheets.

How do I fix formula errors in Google Sheets?

Common errors include #REF! (broken reference), #N/A (value not found), #VALUE! (wrong data type), and #DIV/0! (division by zero). Wrap formulas in IFERROR to handle errors gracefully: =IFERROR(your_formula, "Fallback value").

What formulas does Google Sheets have that Excel doesn't?

Google Sheets has several unique functions: IMPORTRANGE (cross-spreadsheet data), IMPORTHTML/IMPORTXML (web scraping), GOOGLEFINANCE (stock data), QUERY (SQL-like data filtering), and FILTER/SORT/UNIQUE (dynamic array functions that work natively).

How do I apply a formula to an entire column?

Use ARRAYFORMULA to apply a formula to a whole column at once. Instead of writing =A1*B1 and dragging down, write =ARRAYFORMULA(A1:A*B1:B) in a single cell. This automatically expands to fill all rows.