📊 Lesson 2.1: Formula Basics — References, Operators & Absolute vs Relative

What a Formula Actually Is

In Lesson 1.1 we said a spreadsheet stores the logic of your data, not just the answers. A formula is where that logic lives. It's an instruction you type into a cell that tells Sheets to calculate something — and the golden rule is that every formula begins with an equals sign: =. That leading = is how Sheets knows "don't treat this as plain text; run it as a calculation." Type 2+2 into a cell and you'll see the literal text 2+2. Type =2+2 and the cell shows 4. The equals sign is the switch.

Here's the part that surprises newcomers: a cell shows its result, but it remembers the logic. After you type =2+2 and press Enter, the grid displays 4 — but the formula itself is still living underneath. Click that cell and look up at the formula bar (the long strip just above the grid, next to the fx label). The grid shows 4; the formula bar shows =2+2. That's the whole trick of spreadsheets in one picture: the surface is the answer, the formula bar is the truth.

Typing a reference vs clicking a cell

The real power comes when a formula points at other cells instead of fixed numbers. Instead of =2+2, you write =A1+A2. Now the formula says "add whatever is in A1 to whatever is in A2" — and if you later change A1, the answer updates by itself. That address, like A1, is called a cell reference.

There are two ways to put a reference into a formula, and both are worth knowing:

• Type it — literally type the letters and numbers: =A1+A2. Fast once you know the addresses.
• Click it — type =, then click the cell A1 with your mouse, type +, then click A2, then press Enter. Sheets fills in the address for you. This is the beginner-friendly way, and it almost eliminates typos.

Clicking has a lovely side effect: as you build the formula, Sheets highlights each referenced cell in a matching color, so you can literally see which cells your formula is pulling from. Use clicking while you're learning; you'll drift toward typing as the addresses become second nature.

Operators & Order of Operations

An operator is a symbol that tells a formula what kind of math to do. You already know most of them from a calculator; a couple have spreadsheet-specific spellings. Here's the arithmetic family:

The two that trip people up: multiplication is a star * (never the letter x), and division is a slash /. The percent sign is a handy shortcut — 20% is just another way of writing 0.2, so =50*20% gives 10, which is exactly "20 percent of 50."

Order of operations — parentheses are your steering wheel

Sheets follows the same math rules you learned in school (often remembered as PEMDAS): it does Parentheses first, then Exponents, then Multiplication and Division (left to right), then Addition and Subtraction. That means =2+3*4 is 14, not 20 — the multiplication happens before the addition, whether you meant it to or not.

When you want the addition first, wrap it in parentheses: =(2+3)*4 gives 20. Parentheses are how you take control and say "do this part first." When a formula isn't giving the number you expect, missing parentheses are one of the first things to check.

⚠️ Important Note: Every opening parenthesis ( needs a matching closing one ). Sheets colors matching pairs and warns you if they don't balance, but a mismatch is the most common reason a formula won't accept. Count your parentheses when a long formula refuses to work.

Comparison operators — a preview

There's a second family of operators that don't do arithmetic — they ask true/false questions. You'll use these heavily once we reach IF in the next lesson, so here's an early look:

Don't worry about memorizing these yet — just notice that <> means "not equal" (an unusual spelling) and that >= is written with the greater-than sign first. We put them to work in Lesson 2.2.

Relative References — the Ones That Move

Here's a scene you'll live through constantly. You have a table of items with a quantity in column B and a price in column C, and you want a total in column D for each row. In cell D2 you write =B2*C2. Perfect. Now you need the same math in D3, D4, D5, and twenty more rows. Do you retype it twenty times? No — you copy it down, and something wonderful happens.

When you copy =B2*C2 from D2 into D3, Sheets doesn't paste it literally. It becomes =B3*C3. Copy it into D4 and it becomes =B4*C4. The references shifted to keep pointing at the same row as the formula. This is a relative reference: it describes a cell's position relative to the formula, like "the two cells to my left, multiplied together." When the formula moves down a row, "two cells to my left" still means the current row.

To copy a formula down a column, you have three easy options:

• The fill handle — click the cell, grab the little blue square at its bottom-right corner, and drag down over the rows you want to fill.
• Double-click the fill handle — if the column next to it has data, double-clicking that blue square auto-fills all the way down to the last row. A huge time-saver.
• Copy and paste — Ctrl+C the formula cell, select the range below, Ctrl+V.

In every case, the relative references adjust automatically. This is the behavior you'll rely on 90% of the time — and it's precisely why the other 10% needs the idea in the next section. Because sometimes you don't want a reference to move.

The Big Idea: Absolute & Mixed References

This is the most important concept in the module, so we'll take it slowly and it will pay you back for years. Picture the receipt again: each row has a line total in column D, and now you want a tax column that multiplies each line total by a tax rate. The tax rate — say 8% — lives in one single cell, let's call it G1.

In E2 you write =D2*G1. It works for the first row. But when you copy it down, disaster: E3 becomes =D3*G2, E4 becomes =D4*G3… The D part shifting is great (you want each row's own line total), but the G1 part shifting is wrong — G2 and G3 are empty, so your tax collapses to zero. The reference to the tax rate needs to stay put no matter where the formula goes.

The fix is an absolute reference. You add dollar signs to lock the address: $G$1. Now the formula is =D2*$G$1, and when you copy it down it becomes =D3*$G$1, =D4*$G$1 — the D shifts as before, but $G$1 is nailed in place. Every row now multiplies its own line total by the one true tax rate.

The four combinations — and the F4 shortcut

A dollar sign can lock the column, the row, both, or neither. That gives four flavors of reference:

You don't have to type the dollar signs by hand. Click into the reference in the formula bar and press the F4 key: it cycles through the four forms in order — A1 → $A$1 → A$1 → $A1 → back to A1. Tap F4 until you get the locking you want. (On some laptops you may need Fn+F4.)

The mixed references feel abstract now, but they're the secret behind things like multiplication tables and grids where you copy a formula both across and down. For today, the essential skill is the full anchor: lock a single shared constant with $A$1. Here's the whole idea in one picture — one formula copied down a column, with the relative part shifting while the anchored part holds:

Reading & Fixing Formulas

Writing formulas is half the job; reading them back is the other half. Two small distinctions and a short list of error names will save you enormous frustration.

A range vs a single cell

A colon makes a range. A2 is one cell; A2:A10 is "everything from A2 through A10" — nine cells in a block. Ranges are what you feed to functions like SUM (next lesson): =SUM(A2:A10) adds the whole column of values at once. Reading a formula, always notice whether an address has a colon: a colon means a region, no colon means a single cell.

Common typos to check first

• Forgot the leading =. The cell shows your text instead of calculating. The number-one beginner slip.
• Used the letter x for multiply. It has to be *.
• Unbalanced parentheses. Every ( needs its ).
• Comma vs colon. A2:A10 (a range) is not the same as A2,A10 (just those two cells).
• Pointed at the wrong cell. Click the cell and read the formula bar; Sheets highlights the referenced cells so you can see the mistake.

Your first error messages

When Sheets can't complete a formula it shows a short code beginning with #. These aren't scoldings — they're helpful hints about what went wrong. Two you'll meet early:

🎯 Project: A Receipt Calculator

Time to make every idea in this lesson real. You'll build a tiny shop receipt: a few items with quantities and prices, a line total per row, a subtotal that adds them all up, and a tax line that locks onto a single tax-rate cell. Then you'll copy the row formula down and watch the relative references shift while the absolute reference stays anchored. When it clicks, you'll never fear a $ sign again.

       A          B     C       D
1     Item        Qty   Price   Line Total   Line Tax
2     Notebook    3     4.50    =B2*C2       =D2*$G$1
3     Pen         10    1.25    =B3*C3       =D3*$G$1
4     Folder      2     3.00    =B4*C4       =D4*$G$1

7     Subtotal                  =SUM(D2:D5)
8     Tax                       =D7*$G$1
9     Total                     =D7+D8

       F          G
1     Tax Rate    0.08

Best Practices for Writing Formulas

✅ Do's

• Start simple, then copy. Get one row's formula perfect, verify it, then fill down. Fixing one formula beats fixing twenty.
• Click cells while learning. Building references by clicking almost eliminates typos and shows you which cells feed the formula.
• Anchor shared constants. Any value every formula reads — a tax rate, a goal, a rate — should be $A$1 or a named range.
• Read the formula bar. When a number looks wrong, click the cell and check the logic, not just the result.

❌ Don'ts

• Don't bury raw numbers inside formulas. Writing =D7*0.08 hides the tax rate; put 0.08 in a cell and reference it, so you can change it in one place.
• Don't retype a formula many times. If you're typing the same shape twice, you should be copying instead.
• Don't ignore an error code. Hover it, read it, fix the cause — errors are directions, not dead ends.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You built a real, self-updating receipt calculator — line totals, a SUM subtotal, and a tax line anchored to a single rate cell. More importantly, you now own the concept that unlocks the rest of spreadsheets: knowing when a reference should move and when it should stay. That single idea will save you from the most common formula bug there is.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 2.2: Everyday Functions — SUM, AVERAGE, COUNT, IF, ROUND & TODAY — we swap hand-built arithmetic for named, pre-built functions. You'll add, average, count, round, stamp today's date, and make your sheet decide things with IF. The reference skills from today carry straight in.

✅ Before the Next Lesson

• Confirm your receipt recalculates when you change a quantity or the tax rate
• Try pressing Ctrl+` to view all formulas, then toggle back
• Write your Learning Journal entry, especially the relative-vs-absolute explanation

📚 Additional Resources

• Add formulas & functions (Google Support)
• Google Sheets function list (Google Support)
• sheets.google.com — practice in your own sheet