Basic formula syntax
=A2*B2
Use when
Use an equals sign first, then combine numbers, cell references and operators.
Example
If A2 is 12 and B2 is 4, =A2*B2 gives 48.
Back to Spreadsheet Skills
Higher Applications of Mathematics
Spreadsheet Skills reference
Quick reference cards for spreadsheet syntax, references, finance formulae, charts, trendlines and common errors.
Cell references
=B2+C2
Use when
Use cell references so a spreadsheet updates when values change.
Example
If B2 is income and C2 is overtime, =B2+C2 gives total pay.
Absolute references
=B2*$E$1
Use when
Use dollar signs to lock a cell when copying a formula.
Example
If E1 stores VAT rate, =B2*$E$1 copies down without moving E1.
Relative references
=B2-C2
Use when
Use normal references when the row or column should change as the formula copies.
Example
Copying =B2-C2 down becomes =B3-C3, =B4-C4, and so on.
Percentage change
=(new-old)/old
Use when
Use when comparing a new value with its original value.
Example
=(C2-B2)/B2 gives the percentage change from B2 to C2.
Repeated percentage change
=B2*(1+C2)
Use when
Use when a value grows or falls by a percentage each period.
Example
Balance in B2 with 3% in C2 uses =B2*(1+C2).
Present and future value
=B2*(1+C2)^D2 or =B2/(1+C2)^D2
Use when
Use multiplication for future value and division for present value.
Example
Future value: =1000*(1+4%)^5.
Loan repayment table columns
interest = opening balance * rate
Use when
Use columns for opening balance, interest, payment, capital repaid and closing balance.
Example
Capital repaid can be =payment-interest.
Chart and trendline checks
Check labels, units, scale, trendline and R-squared
Use when
Use before interpreting a chart or regression trendline.
Example
A scatter graph should have both axes labelled with units.
Common error messages
#DIV/0!, #VALUE!, #REF!, #NAME?
Use when
Use errors to diagnose the problem in a formula.
Example
#REF! often means a formula points to a deleted cell.