Learning Excel: Formulas
Your first formula
"How much did the small chips bring in?" Fern asks, reaching for her calculator. You stop her: the spreadsheet can do that by itself.
Values and formulas
A cell holds a value, such as text or a number, or a formula: a calculation.
A formula always starts with =. In the cell you see the result; in the formula bar at the top you see the formula itself.
D2 holds a formula like that. The cell shows £179.20, the formula bar shows how it's worked out.
=B2*C2
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Quantity | Revenue | VAT | Incl. VAT | Discount |
| 2 | Small chips | £2.80 | 64 | £179.20 | 6% | 10% | |
| 3 | Large chips | £3.60 | 41 | 6% | |||
| 4 | Battered sausage | £4.50 | 27 | 6% | |||
| 5 | Fish cake | £3.90 | 35 | 6% | |||
| 6 | Curry sauce | £2.20 | 52 | 6% | |||
| 7 | Mushy peas | £0.80 | 70 | 6% | |||
| 8 | Cola | £2.50 | 48 | 21% | |||
| 9 | Water | £2.00 | 22 | 21% | |||
| 10 | Total | ||||||
| 11 |
Calculating with references
=B2*C2 means: take what's in B2 and multiply it by what's in C2. The asterisk * means "times".
B2 and C2 are called references. Each lights up in its own colour, so you can see what the formula calculates with.
The clever part: if Fern changes the price in B2, D2 works itself out again.
=B2*C2
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Quantity | Revenue | VAT | Incl. VAT | Discount |
| 2 | Small chips | £2.80 | 64 | £179.20 | 6% | 10% | |
| 3 | Large chips | £3.60 | 41 | 6% | |||
| 4 | Battered sausage | £4.50 | 27 | 6% | |||
| 5 | Fish cake | £3.90 | 35 | 6% | |||
| 6 | Curry sauce | £2.20 | 52 | 6% | |||
| 7 | Mushy peas | £0.80 | 70 | 6% | |||
| 8 | Cola | £2.50 | 48 | 21% | |||
| 9 | Water | £2.00 | 22 | 21% | |||
| 10 | Total | ||||||
| 11 |
Try it yourself
In D4, work out the revenue from the battered sausages with a formula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Quantity | Revenue | VAT | Incl. VAT | Discount |
| 2 | Small chips | £2.80 | 64 | 6% | 10% | ||
| 3 | Large chips | £3.60 | 41 | 6% | |||
| 4 | Battered sausage | £4.50 | 27 | 6% | |||
| 5 | Fish cake | £3.90 | 35 | 6% | |||
| 6 | Curry sauce | £2.20 | 52 | 6% | |||
| 7 | Mushy peas | £0.80 | 70 | 6% | |||
| 8 | Cola | £2.50 | 48 | 21% | |||
| 9 | Water | £2.00 | 22 | 21% | |||
| 10 | Total | ||||||
| 11 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson