Lesson plan: Data Handling, Spreadsheets and Modelling (ICT)

QCA Unit ICT Data Handling (ICT-1).
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.1c 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 layout of the spreadsheet.

Understand the terms formula, spreadsheet, label, cell, and value.

Be able to write a formula.

Employ a basic model which involves both a formula and

simple formatting.

Most: Will know how to use a spreadsheet.

Some: Will use a spreadsheet to answer questions, i.e. as above and design a more complex formula which performs more than one function.

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.

Resources: PowerPoint, Microsoft Excel 2007, EdExcel, or similar (as available).

Notes: this lesson plan uses Microsoft Excel 2007 and/or EdExcel 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.

Year 7 practitioners will need to bear in mind that learners will have had some experience of spreadsheet operations in Key Stage 2, albeit on much simpler software. It will therefore be necessary to ensure that any misconceptions are addressed in the initial Key Stage 3 sessions. Of particular importance is the single pull-down menu and toolbar, and formatting options.

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

Assessment: see NC Level Descriptors section at the conclusion of this plan.

1. Introduction: explain the learning objectives to the class. Elicit responses to some key questions, i.e. ask the class to explain their understanding of the terms spreadsheet, formula, label, cell, and value. Collate the responses and ask the class, in pairs, to briefly discuss their interpretations. Scribing for the whole group, draw up agreed definitions.

On the interactive whiteboard – with the class at their computers – show the class how to open the spreadsheet programme. Launch Excel 2007, explaining that the software will automatically open the first of three new worksheets, named Sheet 1.

Explain the main features of the Excel 200 worksheet, i.e.,

Office Button. This opens the pull-down menu containing the main file-related commands, , i.e. Save, Open, Exit, and Print. It is also the route to the Excel Options button, which be dealt with in later sessions on changing default settings.

Quick Access Toolbar. This gives access to the Save, Undo and Redo buttons which are all used frequently when editing and saving work.

The Ribbon. The majority of Excel commands are in this feature, visible in the tags from Home through to View.

Formula Bar. This provides the address and contents of the current cell (i.e. the one being highlighted) and allows you to enter formulae for calculations using your data.

Worksheet Area. A grid in which the cells can be identified through letters along the top axis, and numbers down the side. The top cells in each column may be used for column headings. There are vertical and horizontal scroll bars for navigation.

Identify the cells. Highlight a cell and observe that it has a specific cell value. Enter some example data, ask the class to copy it onto their own terminals. Explain that the formula function enables them to carry out a variety of operations, using the right formula. Show the target formula operations, i.e. addition, subtraction, average.

Explain the main tabs on the Excel ribbon, i.e.,

Home. This tab is used for formatting and editing spreadsheets. Its main features are the Clipboard, Font, alignment number styles and editing groups, including cells.

Insert. This is used when adding data in the form of charts, illustrations, tables, pivot tables, hyperlinks, footers and headers.

Page Layout. This includes all of commands needed when preparing a spreadsheet when printing, i.e. page set up, scale to fit etc.

Formulas. This tab is used when adding formulas and functions to spreadsheets it can also be used for checking formula errors. There are four groups in this tab, i.e. Function Library, Defined Names, Formula Auditing, and Calculation Groups.

Data. This tab is used when importing, querying, outlining and subtotalling data. Its main features are as follows; Get External Data, Sort and Filter, Data Tools, and Outline Groups.

Review. This is used when marking up, proofing and protecting spreadsheets which are going to be reviewed by others. Its main features are the Proofing, Comments, ad Changes groups.

View. This tab is used to change the display of the Worksheet area and the data contained in it. It features Workbook Views, Show/Hide, Zoom, Window, and Macros groups.

The basic rule for entering data is that, in order to either enter or edit data, the cell must be ‘current’. This means that…

The cell cursor must be located on the cell in question i.e. a dark border appears around the edges of the cell.

That cell’s address appears in the ‘Name’ box of the Formula bar.

The Basic Excel Formulas.

These formulas enable you to carry out the four operations of addition, subtraction, multiplication, and division, as well as using SUM to quickly calculate the cumulative totals of columns.

Addition: + (plus sign), =A2+B3 Press Return.

Subtraction – (minus sign) =A2-B3 Press Return.

Multiplication * (asterix) =A2*B3 Press Return.

Division / (forward slash) =A2/B3 Pres Return.

Sum =SUM(A2+A3+A4) Press Return.

OR use the Autosum key. Highlight the data to be totalled and press the Autosum button.

However Excel also allows you to build formula which will carry out more than one task. See the example below.

If you need to add to cell values together and then divide the result by the vale of another cell, you could do this by entering the following formula, i.e.

= (A2+A3) / A4 and press return.

The important point here is that once you Save your work including the formulas you have entered, the spreadsheet will remember your instructions. Therefore if you change one or all of the values in the data field, the total or answer will also change.

2. Main Activity: Using the data provided, ask the learners in pairs to use formula to calculate the subtotals and totals of the data and state each subtotal. Calculate the average cost of the items purchased.

3. Plenary: Discuss the implications of today’s activities. Where could learners apply the skills they have learned today? What sort of tasks could be undertaken using spreadsheet software? What sort of organisations could use it? Conversely, what are the limitations of the software? How many different spreadsheet systems can they name? Where have they heard of 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.

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 spreadsheet operation and formula, and through discussion of school-based ICT work during homework tasks.

Originally published on: Essay.uk.com