Let knowledge drive your lessons

Thinking about the knowledge you want students to acquire is fruitful. We spend a great deal of time as teachers planning lessons and sequences of lessons, but perhaps not enough time considering the nature of the cognitive specimen in question.

I am currently teaching Year 7 students how to use spreadsheets. Critical to basic spreadsheet use is the ability to construct simple formula. The simplest formula is adding, subtracting, dividing or multiplying two numbers together. We might construct a formula like this…

=C2+C3

The above formula would instruct an Excel spreadsheet to add together the numbers found in the ‘cells’ named C2 and C3. The answer will appear in the cell where you have typed the formula.

Critical to formula construction is an understanding of the structure of a spreadsheet. Many Year 7 students (at least in my school) have not encountered a spreadsheet before. When I show them one for the first time, I ask what they notice. Immediately they see the ‘grid’ of lines on the sheet, going up and down and side to side. Some notice that the alphabet goes across the top of the sheet and numbers down the side. This is a chance to introduce the technical language that they need. I explain that the spreadsheet is organised in columns and rows, and where these intersect we get ‘cells’. We can insert information in to these cells, often numbers. I tell students that each of these cells has its own name. I point to one cell and ask what they think that cell might be known as. I pause as they struggle with this question, and normally at least a few make the intuitive link and realise that each cell has a unique position, defined by where the column and row intersect, therefore we can use the letter of the column and number of the row to give the cell a name. Finally, I explain that this name is known as the ‘cell reference’, and we always put the column letter first, followed by the row number e.g. C2.

The concept of cell references needs a little practice and checking. This is an opportunity to call upon students to give me the cell reference for any cell I point to, or to come up and point to the cell that I name. If inclined, this is a good moment to employ mini-whiteboards so I can check that every student has got to grips with this idea.

A common mistake at this point is for students to get the number and letter the wrong way round (e.g. 2C), so I draw on the map work they have already been taught in geography, specifically the rule ‘along the corridor and up the stairs’, which we amend to ‘along the corridor and down the stairs’. Drawing parallels to map work can be useful here in reinforcing the idea that every cell on the sheet has a ‘reference’ – this helps students understand that the term reference means a location in both ‘map reference’ and ‘cell reference’.

Once students can identify each cell by its cell reference, we can start to put numbers in to cells. I start with two numbers in cells next to each other, say the numbers 7 and 3. I tell students that I want to add these numbers together and ask them to write this as they would a sum in maths i.e. 7+3=

I then tell students that I want to answer to appear in the next cell. I ask, what should I do?

The answers to this question are interesting. Some students will say ‘type 10 in the cell’. Others will suggest typing the sum ‘7+3=’. This is the moment to introduce (or reintroduce) a fundamental idea in Computing which is that humans have made computers to do work for us. I tell the students that our job is to ‘be lazy’; we want the computer to do as much of the hard work for us as possible. What, I then ask, is the problem with us typing the number 10 in the cell? ‘You want the computer to work it out’ they answer.

And so we have the rationale for using formula; they are a way of getting the computer to do the hard (mathematical) work for us.

For students to understand formula construction they need to know three things;

Unlike in maths, the ‘=’ goes at the start of the sum, not the end. This is because the symbol tells the computer that you want it to carry out a calculation.
Some symbols (+ and -) are the same as in maths, whilst some are different (* and / for multiply and divide, respectively).
We use the cell references in the formula, not the numbers. By using the cell references, the answer will change automatically when we change the numbers in the cells. This is part of ‘being lazy’… we don’t want to keep changing the formula.

Students need to understand why each of the above rules exist, but most importantly they need to get in to the habit of writing formula in this way. It is surprisingly difficult to get students in to good habits and requires deliberate practice. The retrieval from memory of these three rules will need to happen frequently over the coming weeks, also.

The most difficult concept to grasp for most students is the use of the cell reference rather than the number. I think this may have something to do with the abstract idea that numbers are placed in cells and the computer is looking to carry out a calculation using the contents of the specified cells, not using fixed numbers. It can help to translate the formula in to a script, as if you are instructing the computer in plain English. For example;

‘=’ means we are telling the computer we would like it to do a calculation for us.

‘C2+C3’ means we are asking the computer to take whatever number is in cell C2 and add it to whatever number is in cell C3.

There is a great ‘wow’ moment when you press ‘enter’ and the formula magically transforms in to the correct answer. There is an even better one when you change one of the numbers and the answer automatically changes. I have actually had students take a gasp of breath at this point.

What remains is plenty of practice and the revisiting of the key concepts frequently. One of my favorite retrieval questions is to give students four formula which relate to a spreadsheet containing the numbers 7 and 3 in cells C2 and C3:

7+3=
=7+3
=2C+3C
=C2+C3

There are two questions:

Which of these formula would work i.e. give the right answer?
Which of these formula would I prefer you used?

This question draws out some common mistakes, but also distinguishes between what works and what is preferable. Two of the formula work, but all except number 4 highlight a misconception held or mistake commonly made by students.

In the above example, it is the nature of the knowledge which drives the teaching. To understand and be able to construct simple formula, certain prior knowledge is required. Students must understand the structure of spreadsheets, possess the vocabulary to describe the location of information and appreciate the principle that computers are there to serve our bidding. The sequencing of instruction is determined by the need to carefully and meticulously construct this prior knowledge.

Teaching is also influenced by the anticipated misconceptions and mistakes which students are likely to make. Ensuring cell references are given as letter then number requires explicit instruction and an aide memoire. Knowing that students find it difficult to develop the habit of putting the equals sign at the start of the formula and using cell references rather than numbers means that plenty of deliberate practice must be built in. Appreciating how quickly these ideas will be forgotten leads to retrieval practice questions which revisit and emphasise key concepts.

My point is that lessons flow easily from the nature of what is to be taught. Thinking about the specifics of the knowledge students are to acquire is the starting point for planning. Everything should come from this.