Learning Excel: Percentages
Working with percentages
The accountant rings: "Fern, I need your prices with VAT." On chips that's 6%, on soft drinks 21%. Fern sighs. You don't.
A percentage is a number
To Excel, 21% is simply the number 0.21: 21 hundredths. E8 holds 0.21. The sheet shows it as 21% because the column is formatted that way.
That's why you can calculate with percentages like with any other number. =B8*E8 is 21% of the price of a cola: the VAT.
21%
| 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 |
Price with VAT
The price with VAT is the price plus the VAT. You can write that as =B8+B8*E8, or shorter: =B8*(1+E8). That's the price times 1.21.
Mind the brackets. Without them, Excel first works out B8*1 and adds 0.21 to it: a cola at £2.71, and that's not right.
=B8*(1+E8)
| 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% | £3.03 | ||
| 9 | Water | £2.00 | 22 | 21% | |||
| 10 | Total | ||||||
| 11 |
Discount
A discount is the other way round: a percentage comes off. With 10% off, you pay 90% of the price: the price times (1 minus the discount).
Fern gives 10% off to anyone who brings their own container. That percentage is in G2.
10%
| 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 |
Try it yourself
In F2, work out the price of the small chips with VAT, using the rate in E2. Fill the formula down to F9.
| 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