Your First Formulas

How this deck works

Prerequisite knowledge

Meet the two datasets

One skill, two jobs. If you can make a formula work on both sheets, you actually understand it; that transfer is the test this deck keeps setting you.

The old way: a calculator beside the keyboard

The month ends and someone asks for total litres. The familiar routine: read each number off the sheet, punch it into the phone, then type the answer back in. It works, until it doesn't.

Three ways that number goes bad

None of these are carelessness; they're baked into the method. Anything typed by hand is frozen the moment you type it.

Your first formula, one piece at a time

A formula is an instruction you leave in a cell. It always starts with an equals sign, and it points at cells by their addresses rather than copying their numbers. Let's add the Hilux and the grader.

Adding the whole column, the hard way

You could chain every cell together with plus signs. It works, and it already beats the phone calculator, because it updates itself. But five references is tedious here, and a real register might have two hundred rows.

SUM: one word, one range

SUM is a function: a named recipe Excel already knows. You hand it a range of cells in brackets and it adds everything inside. The colon is the key bit of punctuation: B2:B6 reads as "B2 through to B6".

AVERAGE: same shape, different question

"What's a typical fill?" is an AVERAGE question: add everything up, divide by how many there are. You already know how to write it, because it has exactly the same shape as SUM.

The whole point: totals that keep themselves honest

A late docket arrives; the grader actually took 300 litres, not 240. In the old way, this is where the typed total quietly becomes a lie. Watch what the formulas do instead.

Now prove it on the pool sheet

Different sheet, different question, same two moves. The visitors live in B2 to B8 this time; seven rows instead of five, and the formulas don't care.

A new job: a total on every row

Morning tea for the community meeting. Each line needs its own total, quantity times price, and the star * is how Excel writes "times". Start with the first row only.

The fill handle: write once, drag down

Select D2 and look at its bottom-right corner: there's a tiny green square. That square is the fill handle. Grab it and drag down the column, and Excel copies the formula into every row you cross.

Look inside the copies

Click each total and read the formula bar. Nothing was pasted blindly: every copy has quietly re-aimed itself at its own row. A plain reference like B2 really means "two cells to my left", and it keeps meaning that wherever the formula lands. That's called a relative reference.

When copying goes wrong

Now a sheet with one shared number: the GST rate, parked once at the top in B2. Every row should multiply by that same cell. Watch what the fill handle's helpful re-aiming does to it.

The fix: pin the rate with dollar signs

Two dollar signs turn a reference into an absolute reference: one before the column letter, one before the row number. The meaning is simple: when I'm copied, do not move this bit.

The three kinds of reference

Every reference you'll ever write is one of these. The first two carry you through nearly everything; the third is worth recognising when you meet it in someone else's sheet.

Same shape, more questions

You know the shape: name, bracket, range. Here's the rest of the everyday family, each one answering a different question about the same fuel column.

The cheat sheet, keep this one

When it goes wrong: the usual suspects

Now teach it