|
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 |
||||||||||||||
|
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. 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 Download spreadsort 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 |
||||||||||||
|
Spread sheet Vocab 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. |
Manipulating columns and rows video help file Spreadsort (office 2010) |
||||||||||||
|
Learning Objective Lesson 2 |
Lesson Plan |
||||||||||||||
|
Adapting a previously created graph to add axis and titles |
Open Excel and download unfinishedgraphs (Office 2010) 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.
|
Editing chart location video help file |
|||||||||||||
|
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 |
|||||||||||||
|
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?
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 |
Download smile 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
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) |
|||||||||||||
|
Learning Objective Lesson 6 |
Lesson Plan |
||||||||||||||
|
Working with basic addition formulas |
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 Create formula to add manually 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? 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. |
Subtraction formula and how to copy a formula & use it with different data
|
||||||||||||
