Advanced Spreadsheet I – SS2 Digital Technologies Lesson Note (First Term)

Advanced spreadsheet skills let you analyse large amounts of data and make the spreadsheet respond to what you type. They include the IF function for decisions, VLOOKUP for finding information in a long list, Pivot Tables for summarising data, linked worksheets, and Data Validation for preventing wrong entries.

In our basic spreadsheet lessons, we learned how to do simple addition and subtraction. But what happens when you have a thousand rows of data? Or what if you want Excel to “think” and make decisions based on the numbers you type?

This is where Advanced Spreadsheet skills come in. Today, we are moving from being “data typists” to “data analysts.”

Behavioural objectives

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

• Write a simple IF formula to make a decision in a spreadsheet.
• Explain what VLOOKUP does and when it is used.
• Create a Pivot Table to total or average data by category.
• Link a cell on one worksheet to a cell on another worksheet.
• Name three Data Validation rules that prevent mistakes in cells.

What are advanced formulas?

Standard formulas do maths. Advanced formulas use logic. The most important one to master is the IF function.

The IF function

Think of the IF function as a crossroads. You tell the computer: “If this happens, do A. If not, do B.”

The structure:

=IF(Logical_Test,Value_if_True,Value_if_False)

Example in school: Imagine you are calculating exam results. If a student scores 50 or above, they pass. If not, they fail.

• Formula: =IF(A1>=50, “PASS”, “FAIL”)
• If cell A1 is 65, the computer displays PASS.
• If cell A1 is 42, it displays FAIL.

VLOOKUP: the digital librarian

VLOOKUP stands for Vertical Lookup. It is used when you have a huge list and you want to find a specific piece of information based on a “Key.”

Real-life use: When a bank teller types your account number and your name instantly pops up, that is a VLOOKUP in action. It searches down the first column until it finds your ID, then moves across the row to find your name.

What is a Pivot Table?

A Pivot Table is probably the most powerful tool in any spreadsheet. It allows you to take a messy, giant table and summarise it in seconds without typing a single formula.

Why “Pivot”?

It is called a “Pivot” because you can rotate (pivot) the data to see it from different angles.

Scenario: Imagine you have a list of 200 students, their Houses (Red, Blue, Green), and their Scores.

• Want to see the total score for Red House? Pivot it.
• Want to see how many girls are in Blue House? Pivot it.
• Want to see the average score of each house? Pivot it.

How to create a Pivot Table

1. Highlight your entire table of data.
2. Go to the Insert tab and click PivotTable.
3. A sidebar appears with four boxes: Filters, Columns, Rows, and Values.
4. The key step: Drag the category you want to group (like “House”) into Rows, and the numbers you want to calculate (like “Scores”) into Values.

How do you work with multiple worksheets?

In advanced spreadsheets, we do not cram everything onto one page. We use different “Sheets” (the tabs at the bottom) to stay organised.

• Linking sheets: You can bring a total from “Sheet 2” into “Sheet 1” by clicking the cell, typing =, then clicking the cell on the other sheet.
• Why do this? It keeps your “Data Entry” area separate from your “Final Report” area. It is like keeping your rough work in one notebook and your final notes in another.

What is Data Validation?

Have you ever tried to fill a form online and it would not let you type letters in a “Phone Number” box? That is Data Validation. It prevents mistakes by setting rules for cells.

In Excel, you can set rules such as:

• Whole numbers only: (e.g., you cannot have 2.5 students).
• Drop-down lists: (e.g., forcing a user to choose between “Male” or “Female” so they do not misspell it).
• Date limits: (e.g., ensuring a “Date of Birth” is not in the future).

Which tool should you use?

Evaluation

1. Write the structure of the IF function.
2. If cell A1 contains 42, what does =IF(A1>=50, “PASS”, “FAIL”) display?
3. What does VLOOKUP stand for, and what is it used for?
4. Why is it called a Pivot Table?
5. State two rules you can set with Data Validation.

Assignment

1. Open a blank spreadsheet and create a table with 10 names and their “Scores” (out of 100).
2. Use the IF Function to assign a “Grade” to each person (Above 70 = “Excellent”, Below 70 = “Good”).
3. Try to create a Pivot Table that shows the average score of the whole class.