Spreadsheet Planning Can we graph it? Can we manipulate numbers?

Basic spread sheet understanding comes in three strands.

1. Understanding what a spread sheet does

2. Knowing how to graph successfully

3. Knowing how to manipulate numbers using formulas and other techniques

Learning Objective Lesson 1

Lesson Plan

Identify key elements of a spreadsheet (cells, columns, rows and formula’s)

Know how to manipulate rows and columns

Open Excel and ask the children if they have ever seen this type of program before.
If so where? By whom? And for what? (briefly)

Explain that it helps us work with numbers, work out situations and create cool graphs.

Explain that today we are going to look at the main elements of a spreadsheet.

Cells (highlight a cell) Point out the name of the cell a1 or c6, can the children identify other cell names (They could work with a partner to identify cell names taking it in turns) Column highlight a column by clicking where the arrow is show how we can change the width of a column by moving the cursor between each column and left click hold and drag. Row, highlight a row by left clicking on a number to the left show how we can change the height of a row by moving the cursor between the line between numbers and left click hold and drag.

Show how we can select more than one row or column and change the size of all the selected.

Show how we can select all the spreadsheet by using the far left corner

Remind pupils of all the normal formatting tools (briefly)

Activity

Show pupils how they can access the various muddled spreadsheets by using the tabs at the bottom

Set the challenge to un muddle as many spreadsheets in a set time

As pupils are working on this go round and record the names of anyone who is struggling with this

Revise selecting more than one row or column and changing the size of all the selected as this is the aspect that is often forgotten.

Plenary Test class on spreadsheet vocabulary

ICT Skills

Able & SEN Support

Notes & Differentiation

Resources

change row and column size and width

Show more able how to rename the sheets at the bottom (double left click on the tab)

Differentiation by ability. More able pupils will finish all three sheets.

Learning Objective Lesson 2

Lesson Plan

Show the pupils one of the graphs.

Explain that the numbers on the spread sheet are represented by columns on the graph. Change a number and watch the graph column change.

Demonstrate how to modify the graph by right clicking on it and selecting

Chart Type (What type of chart to use line/bar/pie etc) demonstrate with types of bar chart to start with

Move Chart (Into its own page or as an object on the spreadsheet page)

Left click on the graph and move up to the

Layout Tab to add a title and label the axis You may wish to demonstrate one menu option and then give pupils chance to use it. Then come back and demo another before allowing pupils to try it out hence not overloading minds with too much info at the same time (this works best when I have taught this module)

Once they have got the hang of these three skills allow then time to adjust the other graphs on unfinishedgraphs (Office 2010)

This is a great opportunity to emphasise good graphing technique useful in lots of other areas of the curriculum.

Able and SEN support

Notes & Differentiation

· Right click within graph to bring up graph menu

· Selecting correct graph menu choices

· Using the layout tab

Differentiation by ability. More able pupils will finish all three sheets.

unfinishedgraphs (Office 2010)

Learning Objective Lesson 3

Lesson Plan

Knowing how to create a graph from prepared spread sheet data

Know how to format the graph

Explain to the class that they are going to create a simple graph from prepared data on a spreadsheet.

Download graphs and look at the medals sheet and show the class how to create a graph out of the data to include x and y axis labels and titles.

Activity Pupils to create graph from the data on graphs

If they finish they can create graphs from sheets 2 & 3 as well

Explain that you are now going to change the formatting (how it looks) of the graph.

Activity 2 (rest of the lesson)

Pupils need to format the graphs they created in the first part of the lesson

Get pupils to get up and go round and stand by a graph that is very easy to read/understand.

Explain that it is very easy to change a graph and make it look really funky but the primary purpose of a graph is to show the data and if it doesn’t do this then it is not a high level piece of work. I am sure pupils will provide you with lots of examples where the colours are too similar and the work doesn’t stand out.

Print out some on a black and white printer which colours work best.

ICT Skills

Able & SEN Support

***Problem Solving***

Able & SEN Support

· Choosing the correct graph type

· Highlighting the right cells to be made into a graph

· Filling in title and axis details correctly

See if more able can include a label on the bars showing the data value

Blank Graph = Data wasn’t highlighted before the chart was selected. You will need to start again

Empty columns = Blank columns were included in the highlighted selection. You will need to abandon this graph, go back and remove the extra columns or rows and then remake the graph

graphs

Learning Objective Lesson 4

Lesson Plan

Creating your own graph from data provided but which you need to input into the spreadsheet

Prepare before hand a simple test with imaginary results. Have a short key question followed by the data. This works much better if you link this to some science or topic you have been carrying out recently.

Model making a graph out of this data

You could use something like this

Do lighter cats sleep more?

 Cats Amount of Sleep in hours Freckle 3.1kg 12 Herman 3.6kg 11 Poppy 4.1kg 9 Tiddles 4.3kg 8

Teaching points

Of course this is pseudo science and the sample of cats is much too small (great if one of your pupils spots this) if not tell them this at some point.

Notice also that I have chosen to put the weights alongside the cats in the first column this means they will become part of the labels of the bars or columns.

Notice I have not written hours in next to the sleep amounts (Spreadsheets treat cells as number cells or text cells, any letters in a cell means the spreadsheet thinks it is a text cell and will ignore the numbers)

Now allow children time to create their own graph out of the shared data.

In the plenary allow pupils to look at each others and vote (by standing next to) the graph which is clearest hence reinforcing your message form last week that it is not skills but knowing when to use or not use skills that leads to high ICT capability

ICT Skills

Able & SEN Support

***Problem Solving***

Resources

· Transferring data from the interactive whiteboard onto the spreadsheet

· Choosing the correct graph type

· Highlighting the right cells to be made into a graph

· Filling in title and axis details correctly

Have another example ready for pupils who work fast

SEN pupils can spend so much time inputting data that they never create the graph. Consider helping them to speed this process up.

Blank Graph = Data wasn’t highlighted before the chart was selected. You will need to start again

Empty columns = Blank columns were included in the highlighted selection. You will need to abandon this graph, go back and remove the extra columns or rows and then remake the graph

Learning Objective Lesson 5

Lesson Plan

Understanding the relationship between the cell data and the point on the bar or line graph

Creating line graphs using scatter graphs where needed

Open the first sheet and show pupils how to change the graph by changing the numbers in the yellow cells.

Can they create a smile by manipulating the numbers in the yellow cells.

NB You can make a smile by dragging the manipulation points on the graph. This is disallowed and don’t show this to pupils until you have come to the end of this section.

Allow them time to attempt this and the other sections of the smile download where they are asked to create a wave and a rugby ball.

Now demonstrate how to create a line graph

Explain that we would only create line graphs with data where any part on the line could mean something. Demonstrate what happens when you try to use numbers for one of the axis on a line graph. (Both sets of numbers are turned into lines)

EG

Water cooling

 Time in minutes Temperature in degrees 0 61 1 52 2 48 3 46 4 45

Copy this exactly as it is on this chart and select a line graph

It will plot both sets of numbers as lines on the graph.

To get round this show pupils that we can use a scatter diagram. This will position the first columns results as an axis.

Now give them an opportunity to try this for themselves

ICT Skills

Able & SEN Support

Notes & Differentiation

Resources

· Plot a graph using a scatter graph

· Plot a graph using a line graph

You can show more able pupils how to manipulate two sets of numbers to make rugby ball (third sheet)

It would be a good idea to have another line graph example ready for more able pupils.

smile (office 2010)

Line graph video help file

Learning Objective Lesson 6

Lesson Plan

Explain that spread sheets can be made to add, subtract, multiply or divide numbers using a formula.

Go back and open spreadsort that they worked on in lesson one. They may have noticed that the author had highlighted some cells in yellow. Demonstrate how we can use Autosum in these cells to add up all the numbers in a row or column. Give pupils time to do this for the yellow cells in their own versions of spreadsort.

Demonstrate how to get the spread sheet to extend a pattern of numbers by starting the series 1,2,3 or 2,4,6 etc. Highlight the series then release the left mouse button. Move the cursor over the bottom right hand corner of the bottom highlighted cell (arrow). This will change from a white cross to a black cross, when it does left click and drag it down. The series will be continued. Explain that we can create any series of numbers by doing this. Give pupils time to try this. They could create lists of all the tables to look for patterns. Do these patterns go beyond 12x?

Demonstrate how to add two numbers manually by creating a formula Point out

· How every formula starts with a = sign

· How as you type the cell name it is has a colour box around it the same colour as that used in the formula.

· Cells don’t need to be next to each other to be combined.

· Once you have pressed return to finish a formula it can be changed in the formula bar at the top. Give pupils time to try this.

ICT Skills

Able & SEN Support

Notes

Resources

Add up a series of numbers using Autosum

Understand the parts of a formula

Know how to extend a series of numbers

All of the symbols used in Excel are used in computer science when programming.

Extending a pattern of numbers can be used when exploring decimals or fractions in maths

Learning Objective Lesson 7

Lesson Plan

Working with subtraction multiplication and division formulas

Remind pupils how to add using a formula.

Demonstrate how to subtract one number from another using a formula This time instead of typing the cell name left click on it

Give pupils time to subtract some numbers using this method.

Ask them what symbols we will use for multiplication and division?

They will struggle to find ÷ on the keyboard but might suggest an x for multiply.

Explain that we can’t use an x as we then can’t write any word with x in it like extra.

Demonstrate how to create a multiplication formula using an * asterix. Give pupils time to multiply some numbers

Can they multiply three numbers?
Does it matter which order they do these in? (Commutative law explored)

Demonstrate how to divide two numbers using / symbol. Give pupils time to divide two numbers.

Does the order matter when dividing?

Able & SEN Support

Notes

Resources

Create formula to subtract, multiply and divide

Know how to copy similar formulas

For more able mathematicians you can demonstrate the use of brackets to force an operation to complete first. All of the symbols used in Excel are used in computer science when programming.