Using graphs for different statistical tasks – lesson plan

QCA Unit Data Handling : Using Graphs for different statistical tasks 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 sort data insert a graph into a spreadsheet.

Most: Will know how to sort data and 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, average, mean, 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 to the class that, over the next two sessions, they are going to undertake a descriptive statistics task using a database. The subject matter will be at the practitioner’s discretion, but suitable topics could be life expectancy of the two genders in different parts of the world, or something similar.

The sort function. Using the interactive whiteboard, initiate Excel 2007 and open

a pre-prepared database, which would need to be constructed in the following way, i.e.,

State/territory Overall Male Female
UK 80.1 78.1 82.1
Italy 82.0 79.4 84.5
United States 78.2 75.6 80.8
Syria 74.1 72.3 76.1
Angola 38.3 37.24 39.22
Swaziland 31.88 31.62 32.15




Explain that the Sort function allows the sorting of data according to the instructions entered.

1. Highlight the column to be sorted and go to the Data menu on the toolbar.

2. Select Sort.

3. In the sort window, look at the three part menu. Select the Ascending or Descending option to achieve a numerical ascending/ descending column of values.

4. Sort by the ‘Overall’ column to produce a table sorted by this data, i.e.,

State/territory Overall Male Female

























What other ‘sort’ exercises can the class suggest to make the best use of this data?

The class will answer a series of questions based on this data, using the calculator and formula functions of Excel.

Task 1: Sort the data using the Sort function from the Tools menu, first for the Male and then the Female columns.

Task 2: Using the skills learned in an earlier session, make a graph of the data table provided using Excel Chart Wizard.The graph must have the following features.

A suitable title of your choice.

Major or minor gridlines.

A data table appended to the graphics, with a font size of 8 pt.

The legend positioned at the bottom.

Task 3: Review you graph. How successfully/clearly does it display the data?

Example Graph (1).

Task 4. Return to Excel and use the same data to create a different graph type.

Example Graph (2).

Task 5. Critically review both types of graph. What are their comparative strengths and weaknesses? Which conveys the information better, and why?

Plenary: Remind the class that they have now learned another data handling skill, i.e. sorting data. Compile a set of class instructions for that task. Review the graphs created, ask the pairs to report back on their attempts to create different types of graph. Review some examples and ask the class to comment on what worked, and what didn’t. Draw up some class criteria for a successful graph presentation of data.

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 for the same body of data.

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 statistical tasks within graphing.

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 different kinds of graph design.

Originally published on: