Spreadsheet Planning for Year 4 First written October 2007

Can we graph it?

The main aim of this module is for pupils to be so confident creating graphs using Excel that they will ask to use it in other curricular areas

Rationale I have moved all the graphing work into Year 4, which I think from past experience they can cope with. This will allow pupils more chances to use their graphing skills earlier within science, geography etc. I intend to re-write the Year 5 module to concentrate solely on formula and number manipulation which matches closer with their maths curriculum.

Learning Objective Lesson 1

Lesson Plan

Understand what a spreadsheet does

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) show how we can change the width of a column

Row (highlight a row) show how we can change the height of a row

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

Download Spreadsort from the Year 4 Spreadsheet resources or this web site

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

         Spreadsheet Vocab

         change row and column size and width

Show more able how to rename the sheets at the bottom

Show more able how to add up column totals using autosum (fill in yellow cells)

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

 

Spreadsort.xls

Columns & Rows video help file

Learning Objective Lesson 2

Lesson Plan

Adapting a previously created graph

Open Excel and download unfinishedgraphs.xls from this website or the year 4 spreadsheet resources within Abbotswood school

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

Chart Options (This is where we can change the information on the chart)

Location (In itís own page or as an object on the spreadsheet page)

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 graphs on sheet 2 & 3

 

Able and SEN support

Notes & Differentiation

 

         Right click within graph to bring up graph menu

         Selecting correct graph menu choices

 

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

 

Unfinishedgraphs.xls

Graph Label video help file

Learning Objective Lesson 3

Lesson Plan

Knowing how to create a graph from prepared spreadsheet data

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

Open Y4Graph.xls sheet1 either show the class how to create a graph out of the data to include x and y axis labels and titles or show the graphing video

Activity Pupils to create graph from the data on Y4Graph.xls

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

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

Either demonstrate the formatting yourself or play Graph formatting to your pupils

Activity 2 rest of the lesson

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

Conclusion 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.

If there is time they can go back and investigate this.

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 wizard button was selected. You will need to start again (there is a way of sorting this using the source section but it is quite complicated)

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

Y4graph.xls

Graphing video help file

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 you have been carrying out recently.

Model making a graph out of this data

You could use 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

 

Make sure you go round and help anyone with the problems mentioned in the next column. I often mention these as further teaching points.

Blank Graph = Data wasnít highlighted before the chart wizard button was selected. You will need to start again (there is a way of sorting this using the source section but it is quite complicated)

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

 

Download Smile from this website or the Year 4 Spreadsheet resources if you are within Abbotswood School

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

Do heavier teachers jump further?

Weight of teachers in kg

Distance Jumped in M

110

2.5

100

2.3

98

2.7

80

1.6

75

2.9

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

         Plotting a graph using a scatter graph

         Plotting a graph using a line graph

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

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

 

Smile.xls

Learning Objective Lesson 6

Lesson Plan

Provide pupils with the following data on paper or shown via a data projector. (two part assessment)

 

Part 1

Create a graph from this data.

You choose what type of graph to use.

Print it out and then hand it in with your name on it.

Do larger balls bounce higher?

Balls in size order

Height of bounce in cm

Ping pong ball

36

Tennis ball

45

Small football

44

Rugby ball

26

Basket ball

54

 

Part 2

Create a graph from this data.

You choose what type of graph to use.

Print it out and then hand it in with your name on it.

Do heavier balls bounce higher?

Weight of balls in g

Height of bounce in cm

3

56

20

44

100

40

150

33

250

30

Marking Criteria and levelling opportunities

 

If pupils have done both parts start by assessing part 2 first. If that assessment is high then stop there. If it isnít then go on and look at part 1. Results of part 1 will over ride part 2 if it is looked at.

Report Level

Description

Below

Part 1 There are empty columns in the bar chart

Part 2 A line or other graph type was used

Within

Part 1 A bar or column was used but axes or titles were only partially or incorrectly labelled.

Part 2 Scatter graph was used to create a single line of data with the first column as an axis. Axes or title or both are incorrectly labelled.

Above

Part 1 A bar or column graph was used which is easy to read (data stands out) The axes are correctly labelled. Title is correct.

Part 2 Scatter graph was used to create a single line of data with the first column as an axis. Both axes are correctly labelled. Title is correctly inputted.

At Abbotswood we will be moderating the work in the third week after Christmas