Formulas and references

Why it matters

A spreadsheet earns its keep when it calculates for you: totals, rates, conversions, costs with tax. The power comes from references: a formula points at other cells, so when the inputs change, the answer updates. The most common spreadsheet bug is a reference that shifts when the formula is copied and quietly points at the wrong cell.

The core idea: a formula starts with =

Type = and then an expression. Operators: + - * / and ^ (power). Normal maths order applies, so use brackets when in doubt.

=D2*E2          Qty times UnitCost
=(B2-C2)/C2     percent change from C2 to B2
=2^10           1024

Point at cells instead of typing numbers into formulas. =D2*8.5 hides the price inside the formula; =D2*E2 shows where every number comes from.

Relative references move when you copy

When you copy =D2*E2 from row 2 down to row 3, the spreadsheet rewrites it as =D3*E3. The reference is relative: it means "the cells in this same row, columns D and E". This is what you want most of the time. Fill a formula down by dragging the small square at the bottom-right of the selected cell (the fill handle) or by double-clicking it.

Absolute references stay put

Sometimes every row must use one fixed cell, such as a tax rate. Put the rate in its own labelled cell, say H1 = 8%. If you write =F2*(1+H1) in G2 and copy it down, row 3 becomes =F3*(1+H2): H2 is empty, so the answer is wrong without any error message.

Fix it with dollar signs: $H$1 is absolute. The $ before the letter locks the column; the $ before the number locks the row.

=F2*(1+$H$1)    copied down -> =F3*(1+$H$1), =F4*(1+$H$1) ...

Shortcut: click inside the reference in the formula and press F4 (Excel and Google Sheets; on a Mac you may need Fn+F4, or Cmd+T in Excel) or Shift+F4 (LibreOffice). Each press cycles H1 -> $H$1 -> H$1 -> $H1.

Mixed references

H$1 locks only the row; $H1 locks only the column. Mixed references matter when you copy a formula both across and down, for example a multiplication grid:

B2: =$A2*B$1     column A and row 1 are the headers

Copied anywhere in the grid, $A2 always reads from column A and B$1 always reads from row 1.

Worked example: totals and shares

On the Orders sheet from the last lesson:

1. F1: type Total. F2: =D2*E2. Fill down to F11. The first order gives 85.00.
2. F12: =F2+F3+... is tedious, so use =SUM(F2:F11) (more in the next lesson). The grand total is 781.80.
3. G1: Share. G2: =F2/$F$12, formatted as Percent. Fill down. Each order's share of the total, and the column adds up to 100%.

Without the dollar signs, G3 would divide by F13, which is empty, and show #DIV/0!.

Reading error values

Errors are useful: they tell you something is wrong. The dangerous bugs are wrong references that produce a plausible number.

Common mistakes

• Typing constants into formulas (=F2*1.08) instead of referencing a labelled input cell.
• Forgetting $ on a fixed cell, then copying the formula.
• Adding $ everywhere "to be safe", so nothing moves when you fill down.
• Not checking: click a few cells down the column and read the formula bar, or use View > Show > Formulas (Sheets), View > Show Formula (LibreOffice) or Ctrl+` (Excel) to see every formula at once.

Quick recap

• Formulas start with = and should reference cells, not hard-coded numbers.
• Relative references (D2) shift when copied; absolute ones ($H$1) do not; mixed ones lock only the row or the column.
• F4 (Shift+F4 in LibreOffice) cycles through the reference types.
• Spot-check copied formulas and read error values as clues.

Try it

Open the Do tab to add calculated columns to your Orders sheet.