Google Sheets formulas are instructions that begin with an equals sign and tell the sheet to calculate something for you, such as a total, an average or a yes-or-no decision. You do not need to memorize hundreds of them. A dozen well-chosen functions cover most everyday work for freelancers and small businesses.
This guide walks through how a formula is built, the functions to learn first, and how to read the errors Sheets shows when something goes wrong.
How a formula is built
Click a cell, type =, and Sheets knows you are writing a formula. After the equals sign you can use numbers, cell references, operators and functions. For example, =B2*C2 multiplies the values in two cells, and =SUM(B2:B10) adds every value from B2 down to B10.
A function has a name, a pair of parentheses and arguments inside them, separated by commas. Depending on your regional settings, some spreadsheets use semicolons instead of commas, so if a formula from a tutorial fails, check that first. Sheets also suggests functions as you type and shows a small help box with the expected arguments, which is worth reading until the patterns feel familiar.
Cell references and ranges
A reference such as B2 points to one cell. A range such as B2:B10 points to a block of cells. When you copy a formula down a column, the references shift with it, so =B2*C2 becomes =B3*C3 in the next row. That is usually what you want.
Sometimes you want a reference to stay put. Adding dollar signs locks it: $B$1 always points to B1, wherever you copy the formula. This is useful for a tax rate, a conversion factor or any single input that many rows depend on. Pressing F4 while editing a reference cycles through the locked variations.
The Google Sheets formulas to learn first
These functions do most of the heavy lifting in ordinary spreadsheets.
| Formula | What it does | Example |
|---|---|---|
| SUM | Adds numbers | =SUM(B2:B20) |
| AVERAGE | Mean of numbers | =AVERAGE(B2:B20) |
| MIN / MAX | Smallest or largest value | =MAX(B2:B20) |
| COUNT | Counts cells containing numbers | =COUNT(B2:B20) |
| COUNTA | Counts non-empty cells | =COUNTA(A2:A20) |
| ROUND | Rounds to a set number of decimals | =ROUND(B2*1.2, 2) |
COUNT and COUNTA are easy to mix up. COUNT ignores text, so use COUNTA when you want to know how many rows have something in them, such as a list of client names.
Making decisions with IF
The IF function checks a condition and returns one result if it is true and another if it is false. The pattern is =IF(test, value_if_true, value_if_false). For example, =IF(C2>=100, "Free shipping", "Standard") labels each order depending on its value. Text results go in double quotes; numbers and cell references do not.
You can nest IF statements, but long chains get hard to read. For several outcomes, the IFS function is tidier: =IFS(C2>=500, "Large", C2>=100, "Medium", TRUE, "Small"). The final TRUE acts as a catch-all for everything else.
Conditional totals with SUMIF and COUNTIF
Often you want a total for only some rows. SUMIF adds numbers that meet a condition, and COUNTIF counts the rows that do. If column A holds categories and column B holds amounts, =SUMIF(A2:A100, "Travel", B2:B100) totals every travel expense. =COUNTIF(A2:A100, "Travel") tells you how many there are. When you need more than one condition, SUMIFS and COUNTIFS accept several pairs of range and criterion.
Text formulas that save time
Spreadsheets are full of text, and a few small functions keep it tidy.
- TRIM removes extra spaces from the start, end and middle of text:
=TRIM(A2). - UPPER, LOWER and PROPER change capitalization, and PROPER capitalizes each word, which suits names.
- The ampersand (&) joins text:
=A2&" "&B2combines a first and last name. - TEXTJOIN joins many cells with a separator, such as
=TEXTJOIN(", ", TRUE, A2:A6). - SPLIT breaks text apart at a delimiter:
=SPLIT(A2, ",")spreads a comma-separated value across several cells.
These become even more useful once you start cleaning imported data, which we cover in our guide to cleaning messy data in Google Sheets.
Reading common errors
An error message is Sheets telling you what is wrong, not a sign that you have broken the file.
- #DIV/0! means you divided by zero or by an empty cell.
- #N/A usually means a lookup did not find what you asked for.
- #REF! means a reference points to something that no longer exists, often a deleted row or column.
- #NAME? means Sheets does not recognize a function name or text that should be in quotes.
- #VALUE! means a value is the wrong type, such as text where a number is expected.
If an error is expected and harmless, wrap the formula in IFERROR: =IFERROR(B2/C2, 0) returns 0 instead of an error. Use it sparingly, because it can hide real problems. Fix the cause first when you can.
Habits that keep formulas reliable
Keep raw data on one tab and calculations on another, so you never overwrite inputs by accident. Put assumptions such as rates in labeled cells instead of typing them into formulas. Use named ranges, found under Data, when a range is used often, since =SUM(Expenses) is easier to read than a block of coordinates. When a formula looks wrong, select the cell and look at the colored outlines around the cells it references. They show you exactly what it is reading.
Once these basics feel comfortable, the natural next step is finding matching data across tabs, which we explain in our comparison of VLOOKUP and XLOOKUP. You can also put everything you have learned to work in a simple budget spreadsheet, and browse more articles in our spreadsheets section.
Where to start
Open a blank sheet and type ten rows of made-up expenses with a category and an amount. Add a total with SUM, a category total with SUMIF, and a flag with IF for anything above a limit you choose. Twenty minutes of practice on a small, familiar set of numbers will teach you more than reading a long list of functions.
Frequently asked questions
What is the difference between a formula and a function?
A formula is anything you type after an equals sign. A function, such as SUM or IF, is a built-in tool you can use inside a formula.
Why does my formula show as text instead of a result?
The cell may be formatted as plain text, or the equals sign may be missing. Change the format to Automatic and retype the formula.
How do I stop a cell reference from changing when I copy it?
Add dollar signs, as in $B$1, to lock the column and row. Pressing F4 while editing cycles through the options.
Do I need to type function names in capitals?
No. Google Sheets accepts lowercase and fixes the capitalization for you when you press Enter.
