➕ Formulas & Functions
Formulas are what turn a spreadsheet from a fancy table into a powerful calculator. Learn how to write them, use the most important built-in functions, and understand why cell references are the key to everything.
Open sheets.google.com and create a new blank spreadsheet. You'll build a grades dataset during this module. Keep it open as you read — practice each formula as you go. Learning formulas is like learning to ride a bike: you have to try it, not just watch.
Every formula in Google Sheets (and Excel) starts with an equals sign =. This is the signal that tells Sheets: "this isn't a label or a number — it's a calculation."
Notice the colon : — it means "from...to". So A1:A10 is cells A1, A2, A3, A4, A5, A6, A7, A8, A9, and A10 — ten cells at once. This is called a range.
When you type a formula and press Enter, Sheets calculates it and shows the result in the cell. To see the formula again, click the cell — it appears in the formula bar at the top.
| A — Student | B — Midterm | C — Final | D — Average |
|---|---|---|---|
| Alice Johnson | 88 | 92 | (formula here) |
| Bob Martinez | 74 | 80 | |
| Chloe Kim | 95 | 98 | |
| David Okafor | 61 | 70 | |
| Emma Patel | 82 | 85 | |
| Felix Nguyen | 90 | 88 | |
| Grace Lee | 55 | 62 | |
| Hassan Ali | 78 | 75 | |
| Isabella Cruz | 87 | 91 | |
| James Park | 93 | 96 |
Type exactly as shown — A1 = "Student", B1 = "Midterm", C1 = "Final", D1 = "Average". Data goes in rows 2–11. Leave column D empty for now — you'll fill it with a formula.
SUM — Add everything up
Adds all numbers in a range. The most-used function in all of spreadsheet history.
Try it: Click on cell B13. Type =SUM(B2:B11) and press Enter. You should get 803.
AVERAGE — Find the mean
Calculates the arithmetic mean (sum divided by count). Ignores empty cells.
Try it: In B14, type =AVERAGE(B2:B11). You should get 80.3.
COUNT — Count numeric cells
Counts how many cells in a range contain numbers. Empty cells and text are ignored.
Useful when you want to know how many data points you actually have.
COUNTA — Count non-empty cells
Counts cells that are not empty — including text cells. Use this to count names, labels, or any non-blank entry.
COUNT ignores text. COUNTA counts everything that isn't blank.
MIN and MAX — Lowest and highest values
Find the smallest or largest number in a range.
When you copy a formula to another cell, Sheets adjusts the cell references automatically. This is called a relative reference — and it's usually exactly what you want.
🔄 Relative Reference: =B2+C2
When copied down one row, becomes =B3+C3. The reference moves with you. This is the default. Great for repeating a formula down a column.
🔒 Absolute Reference: =$B$2+C2
The $ signs lock the reference. $B$2 will always refer to cell B2, no matter where you copy the formula. Use when you want to multiply everything by a single fixed value.
Mixed references are also possible: $B2 locks the column but not the row; B$2 locks the row but not the column.
How to add $ signs quickly: Click on a cell reference in the formula bar, then press F4 to cycle through: B2 → $B$2 → B$2 → $B2 → back to B2.
Worked Example: Average per Student
In cell D2, type:
This averages Alice's midterm (B2) and final (C2). Now copy cell D2 and paste it into D3:D11. Watch how Sheets automatically changes it to =AVERAGE(B3:C3), =AVERAGE(B4:C4), etc. That's relative references at work.
When Would You Use Absolute? A Tax Rate Example
Suppose cell F1 holds a tax rate (0.08). You want to calculate tax for prices in column E:
If you used =E2*F1 instead and copied down, row 3 would show =E3*F2 — which would be wrong because F2 is empty.
- Click the cell where you want your total — for example, B12 (just below your last data row).
- In Google Sheets: go to Insert → Functions → SUM. Sheets will guess your range and insert
=SUM(B2:B11)automatically. - In Excel: click the AutoSum button (Σ) in the Home tab. It does the same thing.
- Press Enter to confirm. Press Tab instead to confirm and move right to the next cell.
=SUM( and then click and drag to select the range with your mouse. Sheets fills in the range as you drag.Create a new sheet (click the + at the bottom) and enter this sales data:
| A — Product | B — Units Sold | C — Price ($) | D — Revenue ($) |
|---|---|---|---|
| Laptop Stand | 45 | 29.99 | |
| USB Hub | 120 | 15.50 | |
| Webcam | 30 | 79.00 | |
| Keyboard | 60 | 49.99 | |
| Mouse Pad | 200 | 8.99 | |
| Monitor Arm | 22 | 55.00 | |
| Headphones | 75 | 34.99 | |
| Desk Lamp | 90 | 24.00 |
Tasks:
- In column D, write a formula for Revenue = Units × Price. Use a relative reference formula starting in D2:
=B2*C2. Copy it down to D9. - In a summary area (rows 11–16), calculate:
- Total Revenue:
=SUM(D2:D9) - Average Sale:
=AVERAGE(D2:D9) - Number of Orders:
=COUNT(D2:D9) - Max Revenue:
=MAX(D2:D9) - Min Revenue:
=MIN(D2:D9)
- Total Revenue:
- Format your headers in bold with a green background and white text.
= Start Every Formula?You might wonder: why does every formula start with =? The reason is disambiguation. When you type "123" in a cell, is that a number or the text "one hundred twenty-three"? Sheets treats it as a number. When you type "SUM(A1:A10)", is that a label or a function call? Without the =, Sheets treats it as text. The equals sign is your way of saying: "this is a calculation, not data."
Fun fact: In some older spreadsheet programs, you used @ instead of = for functions — for example @SUM(A1..A10) in Lotus 1-2-3. Excel kept backward compatibility for years by accepting both. Google Sheets accepts only =.
- Every formula starts with
=— that's how Sheets knows to calculate instead of display text. - A range like
B2:B11selects multiple consecutive cells at once. SUMadds,AVERAGEfinds the mean,COUNTcounts numbers,COUNTAcounts non-blanks,MIN/MAXfind extremes.- Relative references (default) shift when you copy a formula — great for repeating patterns.
- Absolute references use
$to lock a cell — use when you need a formula to always point to the same cell. - Press F4 while editing a formula to toggle between relative and absolute references.