Data Handling : Using Graphs in Spreadsheets – lesson plan

QCA Unit Data Handling : Using Graphs in Spreadsheets.
Year Group Year 7 Number in class 30
Time for lesson 1 hour No. of computers 15


Subject: Data Handling, Spreadsheets and Modelling.

Key Concepts Covered:

1.1a Using a range of ICT tools in a purposeful way to tackle questions, solve problems and create ideas and solutions of value.

1.1b Exploring and using new ICT tools as they become available.

1.1.c Applying ICT learning in a range of contexts and in other areas of learning, work and life.

2.1a Consider systematically the information needed to solve a problem, complete a task or answer a question, and explore how it will be used.

2.1c Collect and enter quantitative and qualitative information, checking its accuracy.

2.2c Test predictions and discover patterns and relationships, exploring, evaluating and developing models by changing their rules and values.

2.2d Design information systems and suggest improvements to existing systems.

2.2e Use ICT to make things happen by planning, testing and modifying a sequence of instructions, recognising where a group of instructions needs repeating, and automating frequently used processes by constructing efficient procedures that are fit for purpose.

Aims/Learning Outcomes:

All: Will know how to insert a graph into a spreadsheet.

Most: Will know how to use varying functions of the graphing tool.

Some: Will know how to use a graphing tool in a spreadsheet to answer questions.

Resources: Powerpoint, Microsoft Excel 2007, EdExcel or similar (as available), a range of pre-prepared datasets inExcel (3 or 4 should be sufficient).

Note: this lesson plan uses Microsoft Excel 2007 as this is still the most commonly available software for spreadsheet operations. Practitioners will need to establish precisely what product/system is licensed to their IT department/establishment, and vary the lesson content accordingly. (Most editions of Excel, i.e. 2003 and 2007 use the Insert function to open the graphing tool menu).

Keywords: Formula, spreadsheet, sheet(s), row, label, cell, data, command, scroll bar, navigation, cursor, current, graph and value.

Differentiation: By learning outcome, and differentiated tasks activities. Group work at different levels where the tasks are simplified/extended in terms of content and/or language for specific groups. Those in this class who are in lower ability Maths groups to be aided by teaching support staff as available.

Introduction. Explain that the class are going to learn how to use the graphing function of spreadsheets. Graphing is a key method of displaying the data entered into spreadsheet software in an easily communicable form. However, although the software is very useful for converting data into graphic form, it cannot necessarily offer the best graph design for particular data sets. That is the job of the designer – i.e., you! So today’s session is about how to use the graphing tool to the best advantage when displaying data. Some options will be better than others for particular tasks.

Demonstrate to the class how a graph is created.

1. On the interactive whiteboard, open an example Excel spreadsheet.

2. Explain that the way the data is graphed will depend partly on how it is entered into the spreadsheet itself. The software has to be able to recognise the data format in order to transform it into a graph. Show a very simple dataset, such as the one below. Explain that these are the results from a customer satisfaction survey.

Strongly Agree Agree Neutral Disagree Strongly Disagree







3. Highlight all the desired cells and click on Insert on the toolbar, scroll down and select Chart from the menu.

4. The Chart Wizard window will appear with two menus, Chart Type and Chart Sub-Type. Chart Type offers the general categories of chart available in the software, i.e. column, bar, line, pie, XY scatter, area, doughnut, radar, surface, bubble, stock, cylinder, cone, and pyramid. The Chart sub-type offers options within each type, i.e. two and three dimensional effects, multiple layers, separate column etc.

5. Get the class to spend 5 minutes familiarising themselves with these menus on their own shared terminals.

6. Return to the interactive white board and select Next on the Chart Wizard.

7. Step 2 of the Chart Wizard window will appear, offering menus for Data Range and Series. Explain that these can be used to check the chart area being graphed, and the labels attached to data totals.

8. Click Next to get to Step 3 of the Chart Wizard. The class will see that it has a sub-menu of Titles, Axes, Gridlines, Legend, Data Labels and Data Table.

9. Click on Title and enter a suitable name for the graph. Chart Wizard will enter into the chart window after a short delay.

10. Click on Data Table and Chart Wizard will create a table of the results underneath the graph.

11. Click on Legend and Chart Wizard will offer a menu for placement of the Legend, i.e. at the bottom, corner, top, right or left of chart.

12. Click on gridlines and view the options for the X and Y axes, i.e. major or minor.

13. Click on Labels and view the chart with the labels inserted by Chart Wizard.

14. Click Finish and Save.

15. View the completed graph. What do the class notice about it? Check that the titles and numbers in the added table are clear. If not, click on the data table and go to the Format menu on the toolbar. It will say Highlighted Data Table in the menu. Click n this and the Format Data Table will appear. Select Font and the Size, and adjust the size of the font to fit the data table. Click OK to exit.

16. Other size adjustments may be made via the Format menu by first clicking on either the whole chart area or graph.

17. Save the file and give it a suitable name.

17. Ask the class to review the completed graph. How well do they feel it communicates the value of the data? What could be done to improve it?

Main Activity, 20-25 mins: explain that the class (in pairs) will now construct their own graphs from pre-loaded data sets in Excel, following the steps demonstrated in the introduction. Pairs that complete one graph can proceed to the next set of data, completing up to 4 graphs.

Plenary: review the completed graphs and ask some of the pairs to discuss their work. How successfully does their graph present the data? What improvements could be made? Elicit some input for a shared class table of best practice for graph design in Excel.

Level 3.

Pupils use ICT to save information and to find and use appropriate stored information, following straightforward lines of enquiry. They use ICT to generate, develop, organise and present their work. This requirement will be met by the use of appropriate software to design different types of graphs.

They share and exchange their ideas with others. They use sequences of instructions to…achieve specific outcomes. This requirement will be met through working in pairs and in group/class discussions about best practice in respect of the design of different types of graphs.

They make appropriate choices when using ICT based models or simulations to help them find things out and solve problems. They describe their use of ICT and its use outside school. This will be achieved through the appropriate choice of public information system design, and through discussion of school-based ICT work during homework tasks.

Level 4.

Pupils add to, amend and combine different forms of information from a variety of sources. They use ICT to present information in different forms and show they are aware of the intended audience and the need for quality in their presentations. This requirement will be met by achievement of this lesson’s objectives, i.e. the integration of text, image and graphics in graph design.

Level 5.

Pupils select the information they need for different purposes, check its accuracy and organise it in a form suitable for processing. They use ICT to structure, refine and present information in different forms and styles for specific purposes and audiences. This requirement will be met by achievement of this lesson’s objectives, i.e. the integration of text, image and graphics in graph design

Originally published on