Write formulas using cell references and arithmetic operators, and choose between relative and absolute references so formulas copy correctly

What this dot point is asking

This outcome is about writing formulas that calculate from the data in other cells, and choosing the right kind of cell reference so the formula still works when you copy it. You should know that a formula starts with an equals sign, use the arithmetic operators, refer to cells by their address, and understand the difference between a relative reference (which adjusts when copied) and an absolute reference (which stays fixed, shown with dollar signs). In the written paper you write and explain formulas; in the practical you build them.

The answer

What a formula is

A formula tells the spreadsheet to calculate a result. It always begins with an equals sign, for example =B2+C2. The big advantage of using cell references rather than typing numbers is that if the data changes, the result updates automatically.

Arithmetic operators

• + adds
• - subtracts
• * multiplies
• / divides

So =B2*C2 multiplies the value in B2 by the value in C2. The spreadsheet follows the usual order of operations, doing multiplication and division before addition and subtraction, and brackets first of all, so =(B2+C2)*2 adds first then doubles.

Cell references

A cell reference points the formula at another cell. =B2+B3 adds whatever is in those two cells. Because the formula reads the cells live, changing B2 instantly updates the result. This is why spreadsheets are powerful: you build the logic once and the numbers flow through.

Relative references

By default a reference is relative, meaning it is remembered as a direction rather than a fixed spot. When you copy the formula to another cell, the reference shifts by the same amount. Copy =B2*C2 from row 2 down to row 3 and it becomes =B3*C3. This is exactly what you want when every row needs the same calculation on its own data, because you write the formula once and copy it down the column.

Absolute references

Sometimes a formula must always point at one fixed cell, such as a single price or tax rate. An absolute reference locks the cell using dollar signs, for example $E$1. The dollar before the column letter locks the column and the dollar before the row number locks the row, so $E$1 never changes when copied. You can mix them, for example $E1 locks only the column.

Choosing between them

Ask: should this reference move as I copy the formula, or stay put? If it should match each new row or column, keep it relative (B2). If it must always point at the same cell, make it absolute ($E$1). A common pattern is =B2*$E$1, where the row value is relative and the shared rate is absolute.

Examples in context

Example 1. A running total of marks. A teacher writes =B2+C2+D2 in column E to add three test scores per student, then copies it down. Each row adds its own three cells because the references are relative, so one formula fills the whole class.

Example 2. Applying GST to many prices. A shop keeps the tax rate in cell B1 and writes =A2*$B$1 to find the tax on each price, copying it down the column. The rate stays locked as $B$1 for every row, so changing B1 once updates the tax on every item at the same time.

Try this

• Cue. Write a formula in D2 that adds the values in B2 and C2, and say what it becomes when copied to D3. (=B2+C2, which becomes =B3+C3 when copied down, because the references are relative.)

• Cue. A discount rate sits in cell F1 and is used by every row. Explain how to reference it so a copied formula keeps pointing at it. (Make it absolute as $F$1, so the dollar signs lock the cell and it does not drift when copied.)

• Cue. State what the equals sign at the start of an entry tells the spreadsheet to do. (It tells the spreadsheet to treat the entry as a formula and calculate a result, rather than store it as plain text.)