CLASS NOTE: INTRODUCTION TO SPREADSHEET APPLICATIONS I

SUBJECT: DATA PROCESSING
CLASS: SENIOR SECONDARY SCHOOL 1 (SS 1)
TOPIC: SPREADSHEET APPLICATIONS I (INTRODUCTION, FORMULAS, AND CELL REFERENCING)

---

1. COMPREHENSIVE CORE CONCEPTS

What is a Spreadsheet?

A spreadsheet is a powerful software application used for organizing, analyzing, and storing data in a tabular form (rows and columns). Think of a spreadsheet as a digital version of a traditional paper ledger used by accountants. However, unlike paper, a spreadsheet can perform complex calculations automatically, create visual charts, and manage thousands of pieces of information instantly.

In the world of Data Processing, spreadsheets are indispensable. They are used by scientists to track experiments, by teachers to record grades, and by business owners to track profits. The most common examples of spreadsheet software include Microsoft Excel, Google Sheets, LibreOffice Calc, and Apple Numbers.

The Spreadsheet Interface: Rows, Columns, and Cells

To use a spreadsheet effectively, you must understand its "geography."

• Columns: These run vertically (up and down) and are identified by letters (A, B, C, ... Z, AA, AB, etc.).
• Rows: These run horizontally (left to right) and are identified by numbers (1, 2, 3, ...).
• Cell: A cell is the intersection of a row and a column. It is the basic unit where you enter data.
• Cell Address: Every cell has a unique "name" or address based on its column letter and row number. For example, the cell in column B and row 5 is called B5.
• Cell Range: This refers to a group of cells. For example, A1:A10 refers to all cells from A1 down to A10.

Understanding Basic Formulas

The true power of a spreadsheet lies in its ability to do math. A formula is an expression which calculates the value of a cell.

The Golden Rule: Every formula in a spreadsheet MUST start with an equals sign (=). If you type 2 + 2 in a cell, the spreadsheet treats it as text. If you type =2+2, the spreadsheet displays 4.

Mathematical Operators:

• Addition: +
• Subtraction: -
• Multiplication: * (asterisk)
• Division: / (forward slash)

Cell Referencing: The Secret to Efficiency

Cell referencing is the practice of using a cell’s address (like A1) inside a formula instead of the actual number. For example, instead of typing =100 + 200, you would type =A1 + B1.

Why do we do this? If you change the value in cell A1 from 100 to 500, the formula result updates automatically. There are three main types of referencing:

1. Relative Referencing: This is the default. If you copy a formula =A1+B1 from one row to the next, the spreadsheet automatically changes it to =A2+B2. It "relates" to its new position.
2. Absolute Referencing: Sometimes, you want a cell reference to stay fixed even if you copy the formula elsewhere. We use the **dollar sign ($)** to "lock" the cell. For example, `$A$1` will always point to cell A1, no matter where you copy the formula. This is useful for things like tax rates or conversion rates that don't change.
3. Mixed Referencing: This locks either just the row or just the column (e.g., $A1 or A$1).

---

2. REAL-WORLD EXAMPLES

Scenario A: Personal Pocket Money Manager

Imagine you receive ₦5,000 monthly pocket money. You want to track how much you spend on transport, snacks, and mobile data. By setting up a spreadsheet, you can see your "Balance Remaining" update every time you enter a new expense.

• Cell A1: "Allowance" | Cell B1: 5000
• Cell A2: "Transport" | Cell B2: 1500
• Cell A3: "Balance" | Cell B3: =B1-B2

Scenario B: Small Business "Mama Put" Inventory

A food vendor can use a spreadsheet to calculate the total cost of ingredients. If the price of a bag of rice (Cell B2) increases, the vendor only needs to update that one cell, and the total cost of all rice-based meals will automatically recalculate.

Scenario C: School Result Computation

Your class teacher uses spreadsheets to calculate your total scores. Instead of manually adding Test 1 + Test 2 + Exam for 50 students, the teacher writes the formula once for the first student (=B2+C2+D2) and drags it down for the rest.

---

3. PRACTICAL APPLICATIONS (STEP-BY-STEP GUIDES)

How to Create Your First Addition Formula

1. Open Microsoft Excel or Google Sheets.
2. Click on cell A1 and type 50.
3. Click on cell B1 and type 100.
4. Click on cell C1 (this is where we want the answer).
5. Type the equals sign: =.
6. Click on cell A1 (the spreadsheet writes A1 for you).
7. Type the plus sign: +.
8. Click on cell B1.
9. Press Enter. Cell C1 will now show 150.

How to Use the "Fill Handle" (Auto-Fill)

1. Type 1 in cell A1 and 2 in cell A2.
2. Highlight both cells (A1 and A2).
3. Look for the small green/blue square at the bottom-right corner of the selection. This is the Fill Handle.
4. Click and drag it down to cell A10. The spreadsheet will automatically continue the pattern and fill in 3, 4, 5... up to 10.

---

4. SUGGESTED HOME PROJECTS (PROJECT-BASED LEARNING)

Project: "The Family Grocery Budget Tool"

Goal: Create a tool to calculate the total cost of 5 grocery items and apply a 5% "Emergency Savings" fee using absolute referencing.

Materials Needed:

• A computer with Excel/Google Sheets (OR a ruler, pen, and paper drawn as a grid if a computer is unavailable).
• A list of 5 items used at home and their estimated prices.

Procedures:

1. Column A: List the names of the 5 items (e.g., Rice, Beans, Oil, Soap, Onions).
2. Column B: Enter the quantity you need (e.g., 2, 4, 1).
3. Column C: Enter the price per unit.
4. Column D: Calculate the Subtotal. Use the formula =B2*C2. Drag this down for all 5 items.
5. Cell F1: Enter the "Savings Rate" which is 0.05 (5%).
6. Column E: Calculate the Savings Amount for each item. Use absolute referencing to the savings rate: =D2*$F$1.
7. Final Step: Use the AutoSum function or =SUM(D2:D6) to find the total grocery cost.

---

5. LIFE SKILLS INTEGRATION

• Financial Literacy: Understanding spreadsheets helps you manage money effectively. Being able to see where your money goes is the first step to wealth.
• Organization and Accuracy: In the real world, "manual errors" can cost millions. Spreadsheets teach you to build systems that reduce human error.
• Career Readiness:
  • Accounting: Accountants live in spreadsheets.
  • Engineering: Used for structural calculations.
  • Data Science: The foundation of analyzing "Big Data."
  • Entrepreneurship: Managing stock, payroll, and profit/loss.

---

6. ASSESSMENT THROUGH APPLICATION

Option 1: The Logical Test (Formula Writing) Given the following data:

• Cell B4 = 500 (Cost of a book)
• Cell C4 = 2 (Quantity bought)
• Cell G1 = 0.075 (Value Added Tax - VAT)

1. Write the formula to calculate the total cost for the books in cell D4.
2. Write a formula to calculate the VAT amount for these books using absolute referencing to cell G1.

Option 2: The Practical "Live" Challenge Open a new sheet. Create a table of your subjects and your scores in three different tests. Use a formula to calculate your average score for each subject. Format the "Average" column to show only two decimal places.

---

7. STUDENT REFLECTION QUESTIONS

1. Why do we use cell addresses (like A1) in formulas instead of just typing the numbers?
2. Imagine you are building a budget for a huge wedding with 500 guests. How would a spreadsheet be better than a calculator and a notebook?
3. If you copy the formula =$C$1 + A2 from cell B2 to cell B3, what will the formula look like in cell B3? Why did one part change while the other stayed the same?
4. Can you think of one way your parents or guardians could use a spreadsheet to make their work or home life easier?