Class: Senior Secondary School 1 (SSS 1)
Term: 3rd Term
Week: 4
Age: 15 years
Duration: 45 minutes
Subject: Data Processing
Curriculum Theme: Technology
Previous Lesson: .
Topic: Managing Data in Spreadsheets (Creating References, Using Built-in Functions, Sorting and Filtering Data)
Subject Matter: creating cell references such as B10 and C2:H2, using built in functions such as sum average product cumulative frequency, sorting data in ascending or descending order, filtering data using auto filter and custom filter

Specific Objectives

By the end of the lesson, pupils should be able to:

Cognitive Domain:

Define cell reference and explain its importance.
Identify and describe common built-in functions used in spreadsheets.
Explain the concepts of sorting and filtering data.

Affective Domain:

Appreciate the benefits of efficient data management in spreadsheets.
Show interest in using spreadsheet tools for data analysis.

Psychomotor Domain:

Create various types of cell references in a spreadsheet.
Apply built-in functions to perform calculations on data.
Sort data in both ascending and descending order.
Filter data using auto filter and custom filter options.

Collaborate with peers to practice data management techniques.

Reference Materials

The following resources were used in planning this lesson:

9 Years Basic Education Curriculum for Data Processing
State Unified Scheme of Work for Data Processing
Data Processing for Senior Secondary Schools by Adewale & Sons Publishers
Online resources on data processing and spreadsheet applications

Instructional Materials

The teacher will teach this lesson with the aid of:

Computer set with MS Excel software installed
Projector (if available)
Sample datasets for practical exercises

Rationale for the Lesson

This lesson helps pupils understand how to organize, analyze, and present information effectively using spreadsheets. Mastering these skills is important for making sense of large amounts of data and for various tasks in daily life and future careers.

Prerequisite/Previous Knowledge

Pupils have basic knowledge of what a spreadsheet is and how to enter data into cells.

Lesson Content/Board Summary

Managing Data in Spreadsheets

1. Cell References

Cell reference is a way to identify a specific cell or a range of cells in a spreadsheet. It tells the spreadsheet program where to find the values or data that you want to use in a formula.

Examples of cell references include:

B10: Refers to the cell at the intersection of column B and row 10.
C2:H2: Refers to a range of cells starting from C2 and extending to H2 (all cells in row 2 from column C to H).
A1:B5: Refers to a rectangular range of cells from A1 to B5.

2. Built-in Functions

Built-in functions are pre-defined formulas that perform calculations using specific values in a particular order. They save time and reduce errors when performing complex calculations.

Common built-in functions include:

SUM(): Adds all the numbers in a range of cells. E.g., =SUM(A1:A5)
AVERAGE(): Calculates the arithmetic mean of a range of numbers. E.g., =AVERAGE(B1:B10)
PRODUCT(): Multiplies all the numbers given as arguments. E.g., =PRODUCT(C1,C2,C3)
COUNT(): Counts the number of cells that contain numbers within a range. E.g., =COUNT(D1:D20)
MAX(): Finds the largest value in a set of values. E.g., =MAX(E1:E100)
MIN(): Finds the smallest value in a set of values. E.g., =MIN(F1:F50)

Cumulative frequency involves calculating the running total of frequencies, often using functions or formulas.

3. Sorting Data

Sorting data means arranging data in a specific order based on the values in one or more columns. This helps in organizing and analyzing data more effectively.

Data can be sorted in two main ways:

Ascending order: Arranges data from the smallest to the largest value (e.g., A-Z, 1-100, earliest to latest date).
Descending order: Arranges data from the largest to the smallest value (e.g., Z-A, 100-1, latest to earliest date).

4. Filtering Data

Filtering data means displaying only the rows that meet certain criteria and hiding the rows that do not. This helps in focusing on specific subsets of data.

Types of data filtering include:

AutoFilter: A quick way to filter data based on values in a column by selecting options from a drop-down list.
Custom Filter: Allows for more complex filtering criteria using logical operators (e.g., “greater than”, “less than”, “contains”).

Teaching Methods/Instructional Techniques

Discussion, Lecture, Demonstration, Question and Answer, Visual Aids

Instructional Procedures

Step 1: Introduction

Time: 5 minutes
Teaching Skill: Set Induction
Teacher’s Activity: The teacher greets the pupils and reviews the previous lesson briefly. The teacher then asks pupils how they would organize a list of many students by their scores or find only students who scored above 70. This leads to the topic of managing data in spreadsheets.
Pupils’ Activity: Pupils respond to questions and listen attentively.
Learning Point: Pupils are introduced to the relevance of data management in spreadsheets.

Step 2: Explanation of Cell References

Time: 7 minutes
Teaching Skill: Explanation/Demonstration
Teacher’s Activity: The teacher explains what cell references are and why they are important for formulas. The teacher demonstrates on the computer how to create single cell references (e.g., B10) and range references (e.g., C2:H2, A1:B5) using MS Excel.
Pupils’ Activity: Pupils observe the demonstration and take notes.
Learning Point: Pupils understand how to identify and refer to cells and cell ranges.

Step 3: Practical Application of Cell References

Time: 6 minutes
Teaching Skill: Guided Practice
Teacher’s Activity: The teacher provides a simple dataset and guides pupils to practice creating different types of cell references in a spreadsheet. The teacher walks around to provide support and correct errors.
Pupils’ Activity: Pupils practice creating cell references on their computers or in their notebooks.
Learning Point: Pupils gain practical experience in creating cell references.

Step 4: Introduction to Built-in Functions

Time: 7 minutes
Teaching Skill: Explanation/Demonstration
Teacher’s Activity: The teacher explains what built-in functions are and their purpose. The teacher demonstrates common functions like SUM, AVERAGE, PRODUCT, and COUNT with examples on the computer. The concept of cumulative frequency calculation is also introduced.
Pupils’ Activity: Pupils pay attention to the explanations and watch the demonstration.
Learning Point: Pupils understand the concept and basic application of built-in functions.

Step 5: Practical Application of Built-in Functions

Time: 6 minutes
Teaching Skill: Guided Practice
Teacher’s Activity: The teacher provides a dataset and guides pupils to use the demonstrated built-in functions to perform calculations. The teacher monitors their progress and offers assistance.
Pupils’ Activity: Pupils apply the built-in functions to solve given problems on their computers.
Learning Point: Pupils develop skills in using built-in functions for calculations.

Step 6: Sorting and Filtering Data

Time: 8 minutes
Teaching Skill: Explanation/Demonstration
Teacher’s Activity: The teacher explains the concepts of sorting data (ascending and descending) and filtering data (AutoFilter and Custom Filter). The teacher demonstrates both processes using a sample dataset in MS Excel, showing how to arrange data and display specific information.
Pupils’ Activity: Pupils observe the demonstrations and ask questions for clarification.
Learning Point: Pupils learn how to sort and filter data to organize and analyze information.

Step 7: Evaluation/Review

Time: 5 minutes

Teaching Skill: Questioning/Assessment

Teacher’s Activity: The teacher evaluates the learning by asking the following questions:

Define cell reference and give two examples.
Mention three common built-in functions used in spreadsheets.
Explain the difference between sorting data in ascending and descending order.
State two types of data filtering methods.

Pupils’ Activity: Pupils answer orally and in writing.

Learning Point: Pupils demonstrate understanding of the lesson.

Step 8: Conclusion

Time: 1 minute
Teaching Skill: Wrap-up
Teacher’s Activity: The teacher summarizes the key points of the lesson, emphasizing the importance of managing data effectively in spreadsheets. The teacher assigns homework for pupils to practice sorting and filtering data with a given dataset.
Pupils’ Activity: Pupils listen to the summary and copy the homework.
Learning Point: Pupils consolidate their learning and prepare for further practice.

Lesson Keywords

Cell Reference – A way to identify a specific cell or range of cells in a spreadsheet.
Function – A pre-defined formula that performs calculations in a spreadsheet.
SUM – A built-in function used to add numbers in a range.
AVERAGE – A built-in function used to calculate the mean of numbers.
Product – A built-in function used to multiply numbers.
Sorting – Arranging data in a specific order (ascending or descending).
Filtering – Displaying only the rows that meet certain criteria.
Ascending Order – Arranging data from smallest to largest.
Descending Order – Arranging data from largest to smallest.

Differentiation

For pupils who grasp concepts quickly, the teacher can provide more complex datasets or challenge them to use nested functions. For those needing more support, the teacher can offer simplified datasets and provide one-on-one guidance during practical sessions.

Note for teachers using this lesson plan

Ensure that each pupil has access to a computer with MS Excel or a similar spreadsheet program for the practical sessions. Encourage active participation and hands-on practice for better understanding.