Advanced Spreadsheet I SS2 Digital Technologies Lesson Note

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.”

Advanced Formulas

Standard formulas do math. Advanced formulas use Logic. The most important one to master is the IF Function.

1. 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.

1. 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’s a VLOOKUP in action. It searches down the first column until it finds your ID, then moves across the row to find your name.

Pivot Tables

A Pivot Table is probably the most powerful tool in any spreadsheet. It allows you to take a messy, giant table and summarize 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 Secret Sauce: Drag the category you want to group (like “House”) into Rows, and the numbers you want to calculate (like “Scores”) into Values.

Working with Multiple Worksheets

In advanced spreadsheets, we don’t cram everything onto one page. We use different “Sheets” (the tabs at the bottom) to stay organized.

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

Data Validation: Preventing Mistakes

Have you ever tried to fill a form online and it wouldn’t let you type letters in a “Phone Number” box? That is Data Validation.

In Excel, you can set rules for cells:

• Whole Numbers only: (e.g., You can’t have 2.5 students).
• Drop-down Lists: (e.g., Forcing a user to choose between “Male” or “Female” so they don’t misspell it).
• Date Limits: (e.g., Ensuring a “Date of Birth” isn’t in the future).

Summary Table for Students

Practical 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”).

Try to create a Pivot Table that shows the average score of the whole class.