This lesson plan follows on from LESSON PLAN: DATA HANDLING, SPREADSHEETS AND MODELLING (ICT).
| QCA Unit | Data Handling: Designing and Formatting a Spreadsheet in an Excel-based software package. | |||
| 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 ad 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 the essentials of spreadsheet design within Excel.
Use knowledge of the terms formula, spreadsheet, label, cell, and value to help them in designing a spreadsheet.
Write a formula which assists in the use of the spreadsheet.
Use a model which involves both a formula and formatting.
Most: Will know how to design and format a spreadsheet.
Some: Will use a spreadsheet to answer questions using complex formulae which perform more than a single function.
Resources: Microsoft Excel 200,7 EdExcel or similar (as available).
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.
1. Introduction: explain the learning objectives to the class. In a previous session, they learned the basic features of a spreadsheet programme, as well as some formula commands. In this session, they are going to design a spreadsheet from scratch, suitable for handling the data from a survey. The design has to be both clear and functional, capable of handling the data generated by the survey. There are also special conventions to be followed when entering numbers and text into spreadsheets. When entering some kinds of text, such as long titles for columns or rows, there are ways of designing cell formats to suit these.
Revisit some key questions, i.e. ask the class to explain their understanding of the terms spreadsheet, formula, label, cell, and value. Refer to class definitions drawn up earlier where necessary.
On the interactive whiteboard, open Excel and demonstrate the main principles of spreadsheet design to the class, demonstrating to the class how to build a basic spreadsheet.
1. Enter the preferred title of your data table into the first cell, i.e. A1, at the left top of the worksheet.
2. Place the cell cursor in the row below the title cell, and enter the row headings in the first column of the table.
3. Build the first formula that sums columns/rows in the bottom cell/last cell of that data series. Copy it across the other cells in the table.
4. Begin to enter text and numbers. Note that text will always be left-aligned in each cell. Numbers are always right-aligned. If you try to enter a number which is too large for the destination cell, it will display a repeated ‘hash’ symbol, telling you to make the cell larger. Numbers are entered in their normal form. However, if you need to tell Excel that a number is a positive number, it should be prefixed with the + symbol. The same idea applies if it is a negative number, prefix it with the – symbol.
When writing decimal mixed numbers, use a full stop as a decimal point. When writing integers in thousands or above, use a comma to show this in the normal way, i.e. 5,177. The percentage symbol may also be added to a number in the usual way.
5. The data in cells of groups of cells can be re-formatted using the font, font size, colour etc.
The data topic will be the purchase of audio media. Recorded sound is now available in multiple forms. How many of these can the class name, i.e.
- Vinyl
- Cassette
- Audio CD
- DVD
- Mp3.
The purpose of the survey was to establish how a sample of respondents obtain their music. Do they…
Purchase physical media from retail outlets, i.e. HMV or independent record store?
Buy physical media from online retail outlets?
Download audio files from retail sites like iTunes?
Download audio files from non-retail sites?
Obtain shared files from somebody else?
This is the compiled data from the survey on music purchases. It contains information on 6 age group bands, with 100 responses in each category.
| Music Purchases | |||||
| Age Grps | Phys Med High Street | Phy Med Online | D’load Rtail | Download Other | Fileshare |
| 16-19 |
13 |
29 |
11 |
33 |
14 |
| 20-24 |
15 |
35 |
17 |
27 |
6 |
| 25-29 |
11 |
23 |
14 |
17 |
33 |
| 35-39 |
7 |
34 |
23 |
11 |
25 |
| 40-44 |
31 |
21 |
12 |
34 |
2 |
| 45-50 |
9 |
12 |
30 |
23 |
24 |
The main task is to design a spreadsheet suitable for the display and assessment of this data.
The layout of the spreadsheet will require the use of both column and row titles.
The data lists in the spreadsheet will need to be graphed.
Some of the data in the spreadsheet will need to come from separate workbook files.
The data will need to be updated and added to frequently.
The spreadsheet will need to be viewed in both online and printed form.
Long titles will require that columns are widened. With the cell cursor in the necessary column, click on the line between that column and the next in the top bar, i.e. where A, B, C etc appear. Hold the mouse down and slide to the right until the column is wide enough. You may also need to shorten or abbreviate the heading in some cases.
Alternatively, you can use the Text Wrap feature of Excel to re-format the cells in question. Position the cell cursor over the cell you need to work on. Select the View option form the toolbar and select Formatting Pallete. In the Alignment and Spacing window, click the Text Wrap box. Excel should indent the text as well as extending the size of the cell. There is also an option for reducing the text to fit the original cell size.
The Alignment option also allows you to alter the orientation of text to Vertical, Rotate text up, or Angle text downward options.
These effects are show below.
| Music Purchases | |||||
| Phys Med High Street |
Phy Med Online |
D’load Rtail |
Download Other |
Fileshare |
|
| 16-19 |
13 |
29 |
11 |
33 |
14 |
| 20-24 |
15 |
35 |
17 |
27 |
6 |
| 25-29 |
11 |
23 |
14 |
17 |
33 |
| 35-39 |
7 |
34 |
23 |
11 |
25 |
| 40-44 |
31 |
21 |
12 |
34 |
2 |
| 45-50 |
9 |
12 |
30 |
23 |
24 |
Main Activity: in pairs, ask the class to design a spreadsheet using the features they have seen demonstrated. Remind them that the design has to be both clear and functional, capable of handling the data generated by the survey. The formatted design should display the totals for each column, using the formulas the learners have built. (See below).
| Music Purchases | |||||
|
Age Group |
Phys Med High Street |
Phy Med Online |
D’load Rtail |
Download Other |
Fileshare |
| 16-19 |
13 |
29 |
11 |
33 |
14 |
| 20-24 |
15 |
35 |
17 |
27 |
6 |
| 25-29 |
11 |
23 |
14 |
17 |
33 |
| 35-39 |
7 |
34 |
23 |
11 |
25 |
| 40-44 |
31 |
21 |
12 |
34 |
2 |
| 45-50 |
9 |
12 |
30 |
23 |
24 |
|
86 |
154 |
107 |
145 |
104 |
|
Any pair which completes the task before the allotted time runs out can investigate the chart builder programme.
Plenary: View the completed or part-completed spreadsheet designs, and lead a class discussion on which are most effective. Ask each pair to feedback their experiences of using the hardware/software. Ask each group to summarise their understanding and interpretation of the data. What does it say about the subject matter, i.e. the ways in which music is purchased by different age groups? Do they think the data that was captured is very useful? What additional kinds of data would they ask for in order to build up a clearer picture? What other kinds of research methods could have been used, and how could ICT be used to support them?
Relevant NC Level Descriptors.
For assessment purposes, successful completion of this lesson will enable pupils to achieve the following aspects of the ICT National Curriculum Level Descriptors.
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 recognized spreadsheet software to store, display, and interrogate primary or secondary 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 tasks undertaken.
Level 4
Pupils understand the need for care in framing questions when collecting, finding and interrogating information. They interpret their findings, question plausibility and recognise that poor quality information leads to unreliable results. This requirement will be met by considering the way in which the data was collected for the spreadsheet design, and the ways in which it could be interpreted.
They 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 the use of primary and secondary data and the design of a spreadsheet to interpret and display it.
They use ICT based models and simulations to explore patterns and relationships, and make predictions about the consequences of their decisions. They compare their use of ICT with other methods and with its use outside school. This requirement will be met by the use of formulae and commands to interrogate and measure the data. It will also be met by considering how other kinds of research methods could have been used in this work, , and the ways in which ICT be used to support them.
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 could be met by the adaptation of data for different kinds of analysis, i.e. trends in consumer behaviour, forward business planning for retailers, the expansion of online businesses.
They explore the effects of changing the variables in an ICT based model. They discuss their knowledge and experience of using ICT and their observations of its use outside school. They assess the use of ICT in their work and are able to reflect critically in order to make improvements in subsequent work. These requirements could be met by the testing of predictions, i.e. the increase or decrease of sales, demand, costs etc, using formulae and modelling.
Originally published on Essay.uk.com.