Confident Cells

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.

Your turn

In D2, add Grace's Module 1 and Module 2 marks from the training course. Your formula will be copied down for everyone.

ABCDEFG
1EmployeeModule 1Module 2TotalChangePercentageWeighted
2Grace5261970.6%174
3Tom3845751.9%128
4Priya6770385.6%207
5Marek4439-551.9%122
6Nia7176591.9%223
7Ollie29411243.8%111
8Lucy5855-370.6%168
9Hamza4753662.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

ABCD
1ItemPriceQuantityCost
2Pens£2.003
3Pads£4.005
4Tape£1.502
  1. = in . Click the cell for the answer, D2, and start with =, so Excel knows it's a formula.
  2. =B2 in . Click B2, the price. Its reference goes into the formula.
  3. =B2* in . Type * for multiply.
  4. =B2*C2 in . Click C2, the quantity, and press Enter: £2 × 3 = £6.
  5. =B2*C2 in . Copy it down, and each row uses its own cells: D4 holds =B4*C4. One formula does the whole column.

Order of operations

ABCD
1NameTest 1Test 2Average
2Amira6040
  1. =B2+C2/2 in . This looks like the mean of the two tests, but Excel divides before it adds: 60 + 40 ÷ 2 = 80. Wrong.
  2. =(B2+C2)/2 in . 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

  1. In D2, add Grace's Module 1 and Module 2 marks from the training course. Your formula will be copied down for everyone.
  2. In E2, work out how much the mark changed from Module 1 to Module 2 (Module 2 minus Module 1).
  3. 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.
  4. The course counts Module 2 double. In G2, add Module 1 to two lots of Module 2.