Introduction

Begin by recapping the key vocabulary learned in the previous lesson (cell, row, column, formulae).

Ensure the class can recall:

• How to label a cell with a cell reference
• How to group cells (in rows and columns)
• needing to enter ‘=’ at the beginning of a formula
• Where to find + (shift and =), x (*) and ➗ (/)

 

Getting started

Ask students to open the Activity sheet they used last week. They should navigate to Lesson 2 and complete task 1, which is creating a new worksheet for this lesson, and task 2 which is practising using cell references for calculations.

 

Using a formula

We can ask the spreadsheet to do far more complicated calculations.

Ask the children to think of a times table between 13 and 50, e.g. 23.

Do you know any of that times table?

 

2 x ?? = ?? it’s an easy double

10 x ?? = ?

What about 25 x ?? = 

 

Let's see how a spreadsheet can help! 

Open your Excel copy of ‘Demonstration spreadsheet activities’ that you created for the last lesson. Open the tab ‘tricky times tables’.

 

 

Ask the class:

• What do you notice?
• What can you predict about cell A7, A11, A15?
  • Answers 7, 11, 15 these are the numbers that we are multiplying by
• What can you predict about cell B5, B9, B19?
  • Answers 23, 23, 23 this it the number that is being multiplied
• What can you predict about cell C1?
  • Expect = 1 * 23

 

However in this spreadsheet the cell references are being used to build the formula:

 

Answer = A1*B1

 

Show the class how to build a formula using cell references.

 

On the ‘tricky times tables’ tab demonstrate how to use cell references.

Choose a new 'tricky' times table e.g 87.6

 

1. Enter ‘1’ into cell H1 and ‘2’ into cell H2

2. Highlight both cells H1 and H2 and using the small green ‘handle’ (square) at the bottom right hand corner drag down to cell H12

 

 

3. Ask the students - What do you notice?  The spreadsheet has recognised the beginning of a sequence and then extended the sequence at the same recurring interval (+1). What might this be useful?  When might a [school administrator] use this?

4. Next enter 87.6 into cell I1

5. Highlight cell I1 and use the green handle to extend down to I12. What do they notice this time?

 

 

This time the same number is duplicated all the way down as there was no sequence to extend. This can still be really useful and save typing the same information into lots of cells.

 

6. Now click in cell J1 and type =

7. Click in cell H1 followed by ‘*’

 

 

8. Click in cell I1 followed by the ‘enter’ key

 

 

9. The answer to 1 x 87.6 should appear in cell J1

10. Click in J1 and extend column I down to cell I12 using the handle. Ask the class - What has happened now?

 

 

Even though we copied a formula from J1 that had specific cell references in it (=H1*I1), the spreadsheet understands that we wish to apply that same formula to each row below, and so all of the answers should calculate automatically.

 

This same process is demonstrated in this quick video guide, which is also available to the students via their activity sheet.

 

 

Main task

Ask the children to complete task 3 on their Activity Sheet. Here they will create their own tricky times table on the new work sheet they have created in task 1. Remind them to build the formula using cell references and to extend columns of data using the cell handles.

 

Plenary

Ask the class - How can we use a spreadsheet to quickly work out the perimeter of any regular shape if we know that the length of each side is 6.7 cm?

 

 

• They will need to remember that the perimeter is the distance all the way around the shape. 
• Tell the class the length of each side is 6.7cm.
• Encourage the class not to type 1 and 2 into the first 2 cells as they will only begin calculating the perimeter for an equilateral triangle.
• Encourage the class to use the cell handles to quickly extend the data.
• The formula should be = number of sides [cell ref] * 6.7

 

Whoops! The length of the sides is actually 7.6 cm!  How can we quickly correct our answers to fix a measuring error?