Formula basics: practice exercises
= + − × ÷, cell references. 4 exercises on real-looking data, each marked the moment you press Check. This lesson is free, with no account needed.
In D2, add Grace's Module 1 and Module 2 marks from the training course. Your formula will be copied down for everyone.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Module 1 | Module 2 | Total | Change | Percentage | Weighted |
| 2 | Grace | 52 | 61 | 9 | 70.6% | 174 | |
| 3 | Tom | 38 | 45 | 7 | 51.9% | 128 | |
| 4 | Priya | 67 | 70 | 3 | 85.6% | 207 | |
| 5 | Marek | 44 | 39 | -5 | 51.9% | 122 | |
| 6 | Nia | 71 | 76 | 5 | 91.9% | 223 | |
| 7 | Ollie | 29 | 41 | 12 | 43.8% | 111 | |
| 8 | Lucy | 58 | 55 | -3 | 70.6% | 168 | |
| 9 | Hamza | 47 | 53 | 6 | 62.5% | 153 |
How it works
Every formula starts with =. Use cell references like B2 rather than typing numbers in, so the answer updates when the data changes.
Excel follows the usual order of operations, so multiplying and dividing happen before adding and subtracting. Use brackets when you need a different order.
A cell's number format changes how its value looks, not the value itself. A cell formatted as a percentage shows 0.5 as 50%. In Excel, select the cells and click % on the Home tab (or press Ctrl+Shift+%).
=B2*C2 =(B2+C2)/2
Formulas and cell references
A formula always starts with =. After that, you build a calculation from cell references like B2 and the four operations: + add, - subtract, * multiply, / divide.
Use references rather than typing the numbers in. Then the answer updates by itself when the data changes, and the same formula works on every row when it's copied down.
A cell's number format changes how a value looks, not the value. A cell formatted as a percentage shows 0.5 as 50%, so you don't need to multiply by 100.
Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Quantity | Cost |
| 2 | Pens | £2.00 | 3 | |
| 3 | Pads | £4.00 | 5 | |
| 4 | Tape | £1.50 | 2 |
=in . Click the cell for the answer, D2, and start with =, so Excel knows it's a formula.=B2in . Click B2, the price. Its reference goes into the formula.=B2*in . Type * for multiply.=B2*C2in . Click C2, the quantity, and press Enter: £2 × 3 = £6.=B2*C2in . Copy it down, and each row uses its own cells: D4 holds=B4*C4. One formula does the whole column.
Order of operations
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Test 1 | Test 2 | Average |
| 2 | Amira | 60 | 40 |
=B2+C2/2in . This looks like the mean of the two tests, but Excel divides before it adds: 60 + 40 ÷ 2 = 80. Wrong.=(B2+C2)/2in . Brackets are worked out first: (60 + 40) ÷ 2 = 50. Multiplying and dividing come before adding and subtracting, unless brackets say otherwise.
What you'll practise
- In D2, add Grace's Module 1 and Module 2 marks from the training course. Your formula will be copied down for everyone.
- In E2, work out how much the mark changed from Module 1 to Module 2 (Module 2 minus Module 1).
- Both modules are marked out of 80, so the total is out of 160. In F2, turn the total into a percentage. Column F is already formatted as a percentage, so you don't need to multiply by 100.
- The course counts Module 2 double. In G2, add Module 1 to two lots of Module 2.