Introduction

A spreadsheet without formulas is just a grid of numbers you have to add up by hand. Formulas are what turn Google Sheets from a digital notepad into a calculator, a bookkeeper, and a decision-making tool — all in one free app. The good news is that you do not need to learn hundreds of functions. About a dozen formulas cover nearly everything a beginner will do: adding up a column, averaging a set of numbers, checking a condition, looking up a price, or combining first and last names into one cell.
This guide teaches 12 essential Google Sheets formulas with real, working examples you can copy into your own sheet. Every formula here was checked against Google’s official function list in November 2026, so the syntax is correct as written. We use one small example — a coffee shop’s supply order — throughout, so you can see how the formulas work together instead of learning them in isolation.

How Formulas Work: The Basics

Every formula in Google Sheets follows the same pattern:

1. Start with = — the equal sign tells Sheets you are writing a formula, not plain text. Type SUM(A2:A5) without the = and Sheets will just display the text.
2. Use the function name — like SUM, AVERAGE, or IF. A function is a built-in operation that does one job.
3. Add arguments in parentheses — the inputs the function needs, separated by commas. For example, =SUM(A2:A5) adds the numbers in cells A2 through A5.
4. Reference cells, don’t retype numbers — =B2*C2 multiplies whatever is in B2 by whatever is in C2. Change a price later and every formula using it updates instantly. That is the whole point of a spreadsheet.
   A few building blocks to know before you start:

• Cell reference: a single cell, like B2 (column B, row 2).
• Range: a block of cells, written with a colon, like B2:B5 — everything from B2 down to B5.
• Operators: + add, - subtract, * multiply, / divide, & join text together.
• Absolute references: put a $ before a column letter or row number to lock it in place when you copy a formula. $B$2 never moves; B$2 locks the row but lets the column change; $B2 locks the column but lets the row change. Press F4 (or Fn + F4 on some laptops) while editing a formula to cycle through the options.

The Worked Example: A Coffee Shop Supply Order

All the examples below use this small table. Type it into a blank sheet, or just follow along — every result is worked out so you can check your own answers.

	A: Item	B: Price	C: Quantity	D: Total
Row 2	Coffee beans	12.50	4	50.00
Row 3	Milk	3.20	10	32.00
Row 4	Sugar	2.75	6	16.50
Row 5	Cups	4.10	20	82.00

Each Total in column D is Price × Quantity: 12.50 × 4 = 50.00, 3.20 × 10 = 32.00, 2.75 × 6 = 16.50, and 4.10 × 20 = 82.00. The grand total of all four items is 50.00 + 32.00 + 16.50 + 82.00 = 180.50.

1. SUM — Add Up a Column

=SUM(D2:D5)
SUM adds all the numbers in a range. In our example it returns 180.50, the total cost of the supply order. You can also add individual cells (=SUM(D2, D4)) or mix ranges and cells, and SUM simply ignores text and empty cells in the range.
Use it for: totals, subtotals, monthly spending, invoice amounts. If you track a budget, SUM is the formula you will use most. Our guide to How to Track Sales and Expenses in Google Sheets builds a full expense tracker around it.

2. AVERAGE — Find the Mean

=AVERAGE(D2:D5)
AVERAGE returns the arithmetic mean of a set of numbers. Here it returns 45.125 (180.50 ÷ 4), the average cost per item on the order. Note that AVERAGE ignores empty cells and text, so a blank cell does not drag the average down to zero — but a cell containing an actual 0 does count.
Use it for: average order value, average monthly spending, average test scores.

3. COUNT and COUNTA — Count What Is There

=COUNT(D2:D5) returns 4, because COUNT counts only cells containing numbers.
=COUNTA(A2:A5) also returns 4, because COUNTA counts every non-empty cell, including text. Use COUNTA when you want to know how many items are listed; use COUNT when you want to know how many of them have numbers.
Use it for: counting orders, counting how many rows are filled in, checking that a list is complete.

4. IF — Make a Decision

=IF(D2>50, "Big order", "Small order")
IF is the formula that makes decisions. Its syntax is =IF(logical_expression, value_if_true, value_if_false). It tests a condition and returns one value if the test is true and another if it is false. In our table, D2 is 50.00, which is not greater than 50, so the formula returns “Small order”. Applied to D5 (82.00), it returns “Big order”.
IF is also how you build simple status labels: =IF(C2>=10, "Restock soon", "OK") or =IF(B2="", "Missing price", "Ready"). You can nest IFs inside each other for more than two outcomes, but if you find yourself nesting three or four deep, the IFS function (which takes pairs of conditions and values) is usually cleaner.
Use it for: paid/unpaid flags on invoices, pass/fail results, budget warnings. Try it in an invoice by following How to Make an Invoice in Google Sheets.

5. SUMIF — Add Only What Matches

=SUMIF(C2:C5, ">=10", D2:D5)
SUMIF adds up only the values that meet a condition. Its syntax is =SUMIF(range, criterion, [sum_range]). Here it looks at quantities in C2:C5, picks the rows where the quantity is 10 or more (Milk and Cups), and adds their totals: 32.00 + 82.00 = 114.00.
The criterion can be a number, text in quotes, or a comparison like ">=10". You can also match text, for example =SUMIF(A2:A5, "Milk", D2:D5) returns 32.00. If the range you are testing and the range you are adding are the same, you can leave the third argument out.
Use it for: total spending in one category, total sales for one product, totals for one month.

6. COUNTIF — Count Only What Matches

=COUNTIF(C2:C5, ">=10")
COUNTIF counts the cells that meet a condition. In our table it returns 2, because only Milk (10) and Cups (20) have a quantity of 10 or more. Its syntax is =COUNTIF(range, criterion).
Use it for: how many orders were over $100, how many tasks are marked “Done”, how many expenses fall in one category.

7. VLOOKUP — Find a Value in a Table

=VLOOKUP("Milk", A2:B5, 2, FALSE)
VLOOKUP searches down the first column of a range for a key and returns a value from the same row. Its syntax is =VLOOKUP(search_key, range, index, [is_sorted]). Here it finds “Milk” in column A and returns the value from column 2 of the range — the price, 3.20.
The last argument matters. Use FALSE for an exact match, which is what you want almost every time — looking up a product name or an ID. Use TRUE only when your first column is sorted and you want the closest match below the search key, such as a tax bracket or a grade boundary. Leaving it out defaults to TRUE, which is the most common beginner mistake with VLOOKUP: an unsorted list with the default setting returns the wrong row.
Two limits to know: VLOOKUP can only look to the right — the value you want must be in a column to the right of the search column — and it returns only the first match if there are duplicates.
Use it for: pulling a price from a price list, finding a customer’s details from an ID, matching names to departments.

8. XLOOKUP — The Modern Replacement for VLOOKUP

=XLOOKUP("Milk", A2:A5, B2:B5)
XLOOKUP does the same job as VLOOKUP with a simpler setup and fewer traps. Its syntax is =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). It returns 3.20 for “Milk”, just like the VLOOKUP above — but the lookup range and the result range are separate arguments, so it can look left or right, and there is no column number to count. It also has a built-in fourth argument for what to return when nothing matches: =XLOOKUP("Tea", A2:A5, B2:B5, "Not found") returns “Not found” instead of an error.
XLOOKUP was added to Google Sheets in 2023. If you are learning lookups from scratch, start here. If you work with older spreadsheets or share files with Excel users on older versions, VLOOKUP is still worth knowing because it is everywhere.

9. CONCATENATE and & — Join Text Together

=CONCATENATE(A2, " costs $", B2)
CONCATENATE joins strings into one. Its syntax is =CONCATENATE(string1, [string2, ...]). The example returns “Coffee beans costs $12.5”. The & operator does exactly the same thing and is shorter to type: =A2 & " costs $" & B2.
One catch: when you join a number with text, Sheets drops the number’s formatting — 12.50 becomes 12.5. That is where the next formula comes in.
Use it for: combining first and last names, building addresses, creating labels like “Order #104 — Milk”.

10. TEXT — Format Numbers Inside Text

=TEXT(D2, "$#,##0.00")
TEXT converts a number into text in a format you choose. Its syntax is =TEXT(number, format). Applied to 50.00 it returns “$50.00”. Combined with the previous formula, you get a clean label: =A2 & " costs " & TEXT(B2, "$#,##0.00") returns “Coffee beans costs $12.50”.
Useful format codes include "$#,##0.00" for money, "0%" for percentages, and "mmmm dd, yyyy" for dates — for example, =TEXT(TODAY(), "mmmm dd, yyyy") returns today’s date written out in full, like “November 15, 2026”.
Use it for: invoice line descriptions, report headers, any sentence that includes a formatted number or date.

11. TODAY — Get the Current Date

=TODAY()
TODAY returns the current date as a date value, and it updates automatically every day. Its syntax is just =TODAY() — no arguments. Use it in calculations: =TODAY()+30 gives the date 30 days from now, handy for an invoice due date. =TODAY()-B2, where B2 holds a past date, tells you how many days have passed since that date.
Use it for: due dates, days-since tracking, report headers that always show today’s date. If you plan content, pair it with How to Create a Content Calendar in Google Sheets.

12. IFERROR — Clean Up Errors Before Anyone Sees Them

=IFERROR(VLOOKUP("Tea", A2:B5, 2, FALSE), "Not on the list")
IFERROR returns the first argument if it is not an error, and the second argument if it is. “Tea” is not in our table, so the VLOOKUP fails — but instead of showing an ugly error, the cell displays “Not on the list”. Its syntax is =IFERROR(value, [value_if_error]).
Wrap lookups and divisions in IFERROR when other people will see the sheet. It does not fix the underlying problem, but it keeps a draft spreadsheet readable while you are still building it.

Common Errors and How to Fix Them

Every beginner hits these. They look alarming, but each one has a specific cause and a specific fix.

Error	What it means	How to fix it
#DIV/0!	You divided by zero, or by an empty cell. A common case is averaging a range with no numbers yet.	Check the divisor, or wrap the formula in IFERROR: =IFERROR(A2/B2, "—").
#VALUE!	The wrong type of value — usually text where a number was expected, like ="ten"+5.	Find the text cell and replace it with a number, or use a function that ignores text, like SUM.
#REF!	A formula refers to a cell or range that no longer exists, usually because a row or column was deleted.	Undo the deletion, or click the cell and re-point the formula at the right range.
#N/A	A lookup found no match — VLOOKUP or XLOOKUP could not find the search key.	Check the spelling of the search key, or wrap the lookup in IFERROR or XLOOKUP’s built-in missing value.
#NAME?	Sheets does not recognize a function name — almost always a typo, like =SUMM(A2:A5).	Re-check the spelling against Google’s function list. Function names are in English regardless of your locale.
#ERROR!	A syntax problem, such as a missing parenthesis or quote.	Click the cell, read the formula, and count the opening and closing parentheses.

A useful habit: when a formula breaks, click the cell and read it left to right. Most errors are one wrong reference, one missing quote, or one deleted row.

Practice Exercise: Build a Mini Budget in 10 Minutes

The fastest way to make these formulas stick is to use them on your own numbers. Try this:

1. Make a sheet with three columns: Category, Budgeted, Spent.
2. Add five rows of your real spending — groceries, transport, subscriptions, eating out, and one more of your choice.
3. In a fourth column, use =B2-C2 to show what is left in each category.
4. Use =SUM(B2:B6) and =SUM(C2:C6) for the totals, and =AVERAGE(C2:C6) for your average spending per category.
5. Add an IF formula that flags any category where you spent more than you budgeted: =IF(C2>B2, "Over budget", "OK").
6. Add a TODAY-based header with ="Budget as of " & TEXT(TODAY(), "mmmm dd, yyyy").
   Once that works, you have a real budgeting tool — and a reason to keep using it. Our guide to How to Set Savings Goals Using a Simple Spreadsheet shows how to take the same sheet further with a savings target.

Frequently Asked Questions

Do all Google Sheets formulas start with =?

Yes. The equal sign tells Sheets that what follows is a formula to calculate, not text to display. If you forget it, the cell just shows the text you typed.

What is the difference between a formula and a function?

A function is a named, pre-built operation like SUM or IF. A formula is the whole expression you type into a cell, starting with =, which may use one or more functions — for example, =SUM(A2:A10) is a formula that uses the SUM function.

Why does my formula show the text instead of the result?

Usually the cell is formatted as plain text, or there is a space or apostrophe before the =. Select the cell, change its format to Automatic (Format > Number > Automatic), delete the formula, and type it again.

Should I learn VLOOKUP or XLOOKUP first?

Learn XLOOKUP first — it is simpler, can search in either direction, and handles missing values cleanly. Learn VLOOKUP too, because older spreadsheets and many Excel files still use it, and you will run into it.

How do I copy a formula down a whole column?

Click the cell with the formula, then drag the small square at its bottom-right corner down the column. Relative references (like B2) adjust row by row; absolute references (like $B$2) stay fixed. Double-clicking the fill handle fills down as far as the neighboring column has data.

Can I use these formulas in Excel too?

Almost all of them — SUM, AVERAGE, COUNT, COUNTA, IF, VLOOKUP, SUMIF, COUNTIF, CONCATENATE, TEXT, and TODAY work identically in Excel. XLOOKUP works in newer versions of Excel (Microsoft 365 and Excel 2021 onward). A few Google-only functions, like QUERY and IMPORTRANGE, do not exist in Excel.

Bottom Line

Twelve formulas are enough to handle most everyday spreadsheet work: SUM and AVERAGE for totals, COUNT and COUNTA for counting, IF for decisions, SUMIF and COUNTIF for conditional totals, VLOOKUP and XLOOKUP for lookups, CONCATENATE and TEXT for combining and formatting text, TODAY for dates, and IFERROR for keeping things tidy when something goes wrong. Learn them on a small real example — your budget, an order list, a tracker you actually use — and they will stick far better than memorizing a list.
Start with SUM, IF, and VLOOKUP or XLOOKUP. Those three cover the majority of beginner tasks, and everything else in this guide builds on the same ideas: start with =, reference cells instead of retyping numbers, and let the spreadsheet do the arithmetic. Once the basics feel natural, the next step is a real project — a budget tracker, an invoice, or a content calendar — using the guides linked above.