Week 29 – Absolute References and Review of Formulas and Functions:

CLASS OBJECTIVE

Review how formulas and functions work in spreadsheets and understand how absolute references ($) allow us to keep certain cells fixed when copying formulas across a table.

Using absolute references helps spreadsheets perform accurate calculations across multiple rows or columns.

---

CLASS AGENDA

Introduction
Understanding how formulas behave when they are copied.

Short Review
Remembering how formulas, functions, and cell references work.

Explanation
Learning the difference between relative references and absolute references ($).

Demonstration
Observing how absolute references keep values fixed in calculations.

Practice Activity
Students apply formulas and absolute references in a spreadsheet.

Homework
Practice formulas, functions, and absolute references in a small dataset.

---

1. Introduction

Imagine you are calculating the final price of several products, and each product has the same tax percentage.

You write a formula and copy it down the column.

However, the result becomes incorrect.

Why?

Because spreadsheets normally use relative references, which change automatically when the formula is copied.

To solve this problem, spreadsheets use absolute references.

---

2. Short Review

Before learning absolute references, remember these key concepts.

Formula

A formula is a calculation written in a spreadsheet.

Example:

=A1+B1

This formula adds the values in cells A1 and B1.

---

Function

A function is a built-in calculation tool that performs a specific operation.

Examples:

=SUM(A1:A5)
=MIN(A1:A5)
=MAX(A1:A5)
=AVERAGE(A1:A5)
=COUNT(A1:A10)

Functions help us perform calculations more efficiently.

---

Cell Reference

A cell reference tells the spreadsheet which cell contains the data used in a formula.

Example:

A1
B5
C10

These references allow formulas to use values stored in different cells.

---

3. Relative References

By default, spreadsheets use relative references.

A relative reference changes automatically when a formula is copied.

Example formula:

=A1+B1

If the formula is copied downward, it becomes:

=A2+B2
=A3+B3
=A4+B4

The spreadsheet automatically adjusts the references.

This behavior is useful in many situations, but sometimes we need certain cells to remain fixed.

---

4. Absolute References

An absolute reference keeps a cell fixed when a formula is copied.

Absolute references use the $ symbol.

Example:

=$A$1

This means:

• The column A stays fixed
• The row 1 stays fixed

The reference will not change when copied.

---

Relative vs Absolute References

Reference Type	Behavior
Relative reference	Changes when copied
Absolute reference	Stays fixed when copied

---

5. Example Demonstration

The teacher demonstrates a simple table.

Price	Tax	Final Price
100	0.16	?
200	0.16	?
300	0.16	?

We want to calculate the final price including tax.

Example formula:

=A2*$B$1

Explanation:

• A2 changes when copied
• $B$1 stays fixed

When the formula is copied down:

=A3*$B$1
=A4*$B$1

The tax value remains the same, so all calculations work correctly.

Students observe how the spreadsheet keeps the tax cell fixed.

---

6. Quick Review of Functions

The teacher briefly reviews previously learned functions.

Examples:

=SUM(A1:A5)
=MIN(A1:A5)
=MAX(A1:A5)
=AVERAGE(A1:A5)
=COUNT(A1:A10)

These functions also use cell references, which may be:

• Relative
• Absolute

Understanding references helps us build more powerful formulas.

---

7. Practice Activity

Students will complete a spreadsheet exercise where they:

1. Use formulas to calculate totals.
2. Copy formulas across multiple rows.
3. Apply absolute references ($) to keep specific values fixed.

The activity will help students understand how formulas behave when copied.

---

KEYWORDS

Formula
A calculation written in a spreadsheet.

Function
A built-in tool that performs a specific calculation.

Cell Reference
The location of a cell used in a formula.

Relative Reference
A reference that changes when a formula is copied.

Absolute Reference
A reference that stays fixed when a formula is copied.

$ Symbol
Used to create absolute references.

Range
A group of cells in a spreadsheet.

Week 26, 27, and 28 – Conditional Formatting and data validation

CLASS OBJECTIVE

Understand and apply Conditional Formatting in spreadsheets to automatically highlight data based on specific conditions.

Students will also learn the basic concept of Data Validation to control the type of information entered in a spreadsheet.

These tools help users identify patterns, reduce errors, and analyze information more efficiently.

---

CLASS AGENDA

Introduction

Understanding how spreadsheets can automatically highlight important data.

Short Review

Remember what a spreadsheet cell, value, and range are.

Explanation

What Conditional Formatting and Data Validation are and why they are useful.

Demonstration

Creating rules in Google Sheets to automatically highlight data.

Practice Activity

Students apply formatting rules and create controlled input lists.

Homework

Apply conditional formatting and data validation to a small dataset.

---

1. Introduction

Imagine you have a list of student grades.

Instead of reading every number one by one, a spreadsheet can automatically highlight the most important information, such as:

• Grades higher than 70

• Grades lower than 60

• Students who passed or failed

This is possible thanks to a tool called Conditional Formatting.

---

2. Short Review

Before learning this topic, remember the following concepts:

• Cell: A single space in a spreadsheet where data is written.

• Range: A group of cells (example: A1:A10).

• Data: Information stored in a spreadsheet (numbers, text, dates).

Spreadsheets allow us not only to store information but also to analyze and organize it visually.

---

3. Explanation: What is Conditional Formatting?

Conditional Formatting is a tool that automatically changes the appearance of cells when they meet a specific rule or condition.

This helps users quickly identify patterns or important values.

Example

A spreadsheet can automatically:

• Turn grades green if they are greater than 70

• Turn grades red if they are lower than 60

• Highlight specific words such as “Approved”

Instead of searching manually, the spreadsheet highlights the data instantly.

---

Basic Comparison Operators

Conditional formatting rules often use comparison operators.

Operator	Meaning
>	Greater than
<	Less than
=	Equal to
<>	Not equal to

These operators allow spreadsheets to evaluate conditions automatically.

---

4. What is Data Validation?

Data Validation is another spreadsheet tool that controls what type of data can be entered into a cell.

Instead of allowing any value, you can define rules for input.

For example:

• Allow only numbers between 0 and 100

• Allow only specific words such as Approved / Failed

• Create a dropdown list of options

Why is Data Validation useful?

• Prevents mistakes in data entry

• Keeps information organized

• Makes spreadsheets easier to analyze

Conditional Formatting shows patterns, while Data Validation controls the quality of the data.

---

5. Examples

Highlight numbers greater than 70

Example rule:

Highlight values greater than 70 in a grade column.

Result: High grades become visually easier to identify.

---

Highlight numbers lower than 60

Example rule:

Highlight values less than 60.

Result: It becomes easier to identify students who may need support.

---

Highlight specific text

Example rule:

Highlight cells containing the word “Approved”.

---

Apply a color scale

A color scale automatically changes the color depending on the value.

Example:

Low numbers → red

Medium numbers → yellow

High numbers → green

This helps visualize patterns in large datasets.

---

6. Demonstration (Google Sheets)

Pay attention while the teacher shows the process step by step.

Students observe:

1. Selecting a range of cells

2. Opening Format → Conditional Formatting

3. Choosing a condition

4. Selecting a color style

5. Applying the rule

Examples demonstrated:

• Highlight numbers greater than 70

• Highlight numbers less than 60

• Highlight text equal to “Approved”

• Apply a color scale

Students also observe how formatting updates automatically when values change.

---

7. Practice Activity – “Highlight the Data”

Students will work with a small spreadsheet containing grades or preferences.

They must apply at least three conditional formatting rules, such as:

• Highlight grades greater than 80

• Highlight grades less than 60

• Highlight cells with the word Approved

Students will also create one Data Validation rule, for example:

• A dropdown list with options:

  Approved / Failed

This ensures consistent data entry.

---

9. Notes in Your Notebook

Answer the following questions:

1. What is Conditional Formatting?

2. What is a rule in conditional formatting?

3. What are comparison operators used for?

4. What is Data Validation?

5. How can these tools help analyze information faster?

---

KEYWORDS

Conditional Formatting

A spreadsheet tool that changes the appearance of cells based on a rule.

Rule

A condition that determines when formatting is applied.

Condition

A logical test used to evaluate data.

Comparison Operators

Symbols used to compare values (> < = <>).

Data Validation

A tool that restricts the type of data that can be entered into a cell.

Range

A group of cells in a spreadsheet.

Week 25 - Evaluation week!

 

Weeks 23 & 24: Function COUNTIF() – Conditional Counting

CLASS OBJECTIVE

Learn how to use the COUNTIF() function to count cells that meet a specific condition and apply it to analyze simple data in spreadsheets.

🗓  CLASS AGENDA

💬  Introduction

“Imagine you have a list of grades and you want to know how many students passed.

Instead of counting one by one, the spreadsheet can count only the ones you choose.”

---

1️⃣  Short Review

Students recall:

• What a function is

• What a range is (A1:A10)

• What counting functions do (COUNT, COUNTA, COUNTBLANK)

---

2️⃣  Explanation: What is COUNTIF()?

COUNTIF() counts cells that meet a specific condition.

📌 Structure:

=COUNTIF(range, criteria)

• Range → Where we look

• Criteria → What we are looking for

💡 Simple definition:

“COUNTIF counts how many cells match a rule.”

---

3️⃣  Examples

🔹 Count how many students passed:

=COUNTIF(A1:A10, "Passed")

🔹 Count how many grades are greater than 70:

=COUNTIF(A1:A10, ">70")

🔹 Count how many times the word “Yes” appears:

=COUNTIF(B1:B20, "Yes")

Something to keep in mind :

• Text criteria must go inside quotation marks

• Numbers with symbols also use quotes (”>70”)

---

4️⃣  Demonstration:

Pay a lot of attention while your teacher shows you step by step:

• Writing the formula

• Selecting a range

• Using text criteria

• Using number conditions (> < =)

• Changing values and watching results update

Please observe how the result changes automatically.

---

5️⃣  Activity – “Count With a Rule”

Students will use COUNTIF() in a small table (grades, attendance, preferences) to answer simple questions.

---

6️⃣ 

Homework

Students will complete a short spreadsheet using COUNTIF() with at least three different criteria.

---

7️⃣ Take notes in your notebook.

📓 NOTES IN YOUR NOTEBOOK

Answer these questions:

1. What does COUNTIF() do?

2. What are the two main parts of COUNTIF()?

3. Why do we use quotation marks in some criteria?

4. Write one example of COUNTIF() using numbers.

5. How can COUNTIF() help you as a student?

⸻

📌  KEYWORDS

• Function: A built-in calculation tool

• Range: A group of cells (A1:A10)

• Criteria: A condition used in a function

• COUNT(): Counts numbers

• COUNTA(): Counts non-empty cells

• COUNTBLANK(): Counts empty cells

• COUNTIF(): Counts cells that meet a condition

• Quotation marks (” “): Used for text or comparison criteria

Weeks 21 and 22: Review & Practice – Spreadsheets (Sheets)

CLASS OBJECTIVE

Review and strengthen the use of basic spreadsheet tools — data types, basic formulas, functions, and counting — to help students feel confident working with spreadsheets independently.

🗓  CLASS AGENDA

💬 Introduction

“We have learned many tools in spreadsheets. Today is about practicing and making everything easier, not harder.”

---

1️⃣ Short Review

Teacher reviews with students:

• What a spreadsheet is

• Basic elements:

  • Cell

  • Row

  • Column

• Difference between:

  • Data (text, numbers)

  • Formulas

---

2️⃣ Explanation: What We Know So Far

Teacher briefly reviews:

• Formulas

  • Start with =

  • Use operators (+ – * /)

• Functions

  • SUM()

  • AVERAGE()

  • MIN()

  • MAX()

• Counting functions

  • COUNT()

  • COUNTA()

  • COUNTBLANK()

3️⃣ Demonstration

Teacher opens a simple spreadsheet and demonstrates:

• Writing a basic formula

• Using one function (SUM or AVERAGE)

• Using one counting function

• Changing a value and watching the result update

Students observe and ask questions.

---

4️⃣ Activity – “Practice Makes Easy”

Students will practice with a simple table combining data, formulas, functions, and counting.

---

5️⃣ Take notes in your notebook.

📓  NOTES IN YOUR NOTEBOOK

Answer these questions:

1. What is the difference between data and a formula?

2. What function helps you calculate an average?

3. When would you use COUNTBLANK()?

4. Why is it important to review spreadsheets?

⸻

📌 

KEYWORDS

• Spreadsheet: A digital table for organizing data

• Data: Information written in cells

• Formula: A calculation that starts with =

• Function: A built-in calculation

• COUNT(): Counts numbers

• COUNTA(): Counts non-empty cells