Skip to content

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

Lesson note at a glance

Class
SS2
Subject
Digital Technologies
Term
First Term
Reading time
7 min
Start of this note, 2 of 7
Take this term’s notes offline
Reading here is free. Download all 7 SS2 Digital Technologies First term notes as PDF to print and Word to edit.
Download all three terms, ₦2,000
Instant download. Secure payment by Paystack. Card, bank transfer, USSD or OPay. Payment stuck? WhatsApp us.

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.

Behavioural objectives

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

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

Why do we need advanced spreadsheet skills?

Advanced spreadsheet skills help you handle large data sets, make decisions with formulas and avoid entry errors.

  • Large data: a school list of 1,000 rows is too long to add or check by hand.
  • Decisions: a formula can show PASS or FAIL for you, based on the number typed.
  • Accuracy: rules in cells stop wrong entries before they spoil the results.

What is the IF function?

The IF function tests a condition and shows one result if it is true and another result if it is false.

Standard formulas do maths. Advanced formulas use logic, and IF is the most important logical function.

What is the structure of the IF function?

=IF(Logical_Test, Value_if_True, Value_if_False)

  • Logical_Test: the condition the computer checks, such as A1>=50.
  • Value_if_True: what the cell shows when the test is true.
  • Value_if_False: what the cell shows when the test is false.

Which comparison signs are used in a logical test?

Sign Meaning Example
> Greater than A1>70
< Less than A1<50
>= Greater than or equal to A1>=50
<= Less than or equal to A1<=39
= Equal to A1=100
<> Not equal to A1<>0

How do you write an IF formula?

  1. Click the cell where you want the answer.
  2. Type =IF( to start the formula.
  3. Type the test, for example A1>=50, then a comma.
  4. Type the result for true, then a comma. Put words inside quotation marks.
  5. Type the result for false, close the bracket and press Enter.
Advertisement

What is an example of the IF function?

A student passes with 50 or above and fails with a score below 50.

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

What is a nested IF?

A nested IF places one IF inside another so that a cell can choose between more than two results.

  • Formula: =IF(A1>=70, “A”, IF(A1>=60, “B”, IF(A1>=50, “C”, “F”)))
  • Score 75: displays A.
  • Score 55: displays C.
  • Score 40: displays F.
  • Note: the grade boundaries here are an example. Use your school’s own scale.

What is VLOOKUP?

VLOOKUP, short for Vertical Lookup, searches down the first column of a table and returns a matching value from another column in the same row.

  • Use: finding one record in a long list using a key, such as a student ID.
  • Real-life example: a bank system takes your account number and shows your name. This works in the same way.

What is the structure of VLOOKUP?

=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, Range_Lookup)

  • Lookup_Value: the key you are searching for, such as an ID typed in cell E1.
  • Table_Array: the block of cells that holds the list, such as A2:C200.
  • Col_Index_Num: the column number to return a value from, counting the first column of the table as 1.
  • Range_Lookup: type FALSE for an exact match.

How do you use VLOOKUP?

Suppose column A holds student IDs, column B holds names and column C holds scores.

  1. Type the ID you want to find in cell E1.
  2. Click cell F1 and type =VLOOKUP(E1, A2:C200, 2, FALSE).
  3. Press Enter. The cell shows the name that belongs to that ID.
  4. Change the column number to 3 to show the score instead.
Advertisement
  • Key rule: the ID you search for must be in the first column of the table.
  • #N/A: this error appears when the ID is not found in the list.

What is a Pivot Table?

A Pivot Table is a tool that summarises a large table by grouping the data and calculating totals, averages or counts without typing formulas.

Why is it called a Pivot Table?

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

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

  • Total score for Red House: put House in Rows and Score in Values, and use Sum.
  • Number of girls in Blue House: put House in Rows, Gender in Columns, and a count in Values.
  • Average score of each house: put House in Rows and Score in Values, and use Average.

What are the four areas of a Pivot Table?

  • Filters: shows only part of the data, such as one class.
  • Columns: places categories across the top, such as Gender.
  • Rows: places categories down the side, such as House.
  • Values: holds the numbers to calculate, such as Score.

How do you create a Pivot Table?

  1. Highlight your entire table of data, including the column headings.
  2. Go to the Insert tab and click PivotTable.
  3. Click OK. A sidebar appears with the four areas.
  4. Drag the category you want to group (like House) into Rows.
  5. Drag the numbers you want to calculate (like Score) into Values.
  6. To change from Sum to Average, click the field in Values, choose Value Field Settings and select Average.
  • Refresh: a Pivot Table does not update by itself when the data changes. Right-click it and choose Refresh.

How do you work with multiple worksheets?

A workbook can hold many worksheets, shown as tabs at the bottom of the screen, so that your work stays organised.

Advertisement
  • Data Entry sheet: holds the raw figures, like rough work in one notebook.
  • Final Report sheet: holds totals and results, like final notes in another notebook.

How do you link a cell to another worksheet?

  1. Click the cell on the sheet where you want the result.
  2. Type =.
  3. Click the tab of the other sheet, for example Sheet2.
  4. Click the cell you want, for example B5, and press Enter.
  • Result: the formula reads =Sheet2!B5. The sign ! separates the sheet name from the cell.
  • Sheet names with spaces: they appear in single quotes, for example =’Data Entry’!B5.
  • Benefit: when the cell on Sheet2 changes, the linked cell updates by itself.

What is Data Validation?

Data Validation is a feature that sets rules for a cell so that only correct entries are accepted.

An online form that will not let you type letters in a Phone Number box uses the same idea.

What rules can Data Validation set?

  • Whole number: accepts only whole numbers, so you cannot enter 2.5 students.
  • Decimal: accepts numbers with decimal places, within limits you set.
  • List: creates a drop-down list, such as Male or Female, so nobody misspells it.
  • Date: limits dates, so a Date of Birth cannot be in the future.
  • Text length: limits the number of characters, for example 11 digits for a phone number.

How do you set a Data Validation rule?

  1. Select the cells you want to control.
  2. Go to the Data tab and click Data Validation.
  3. In the Settings tab, choose the type of entry under Allow.
  4. Set the limits or type the list items, separated by commas.
  5. Optionally, use the Input Message and Error Alert tabs to guide the user.
  6. Click OK.

Which tool should you use?

Use IF to decide, VLOOKUP to find, a Pivot Table to summarise and Data Validation to limit entries.

Advertisement
Tool What it does Best for…
IF Function Makes a decision. Grading (A, B, C, F).
VLOOKUP Finds data in a big list. Finding a student’s record by ID.
Pivot Table Summarises giant tables. Finding house totals or averages.
Data Validation Limits what can be typed. Avoiding spelling errors in forms.

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

Build a small class result sheet and analyse it with the tools from this lesson.

  1. Open a blank spreadsheet and create a table with 10 names and their “Scores” (out of 100).
  2. Use the IF function to give each person a “Grade”: 70 and above = “Excellent”, below 70 = “Good”.
  3. Create a Pivot Table that shows the average score of the whole class. Put Score in Values and change it to Average.

This term's Digital Technologies topics

SS2 First Term · 7 topics

  1. Advanced Word Processing Tools
  2. Advanced Spreadsheet I
  3. Advanced Spreadsheet II
  4. Advanced Presentation Tools
  5. Web Design I
  6. Web Design II
  7. Web Publishing and Hosting
Advertisement

Lesson Notes for Other Classes

Print it or edit it in Word

Every note is free to read online. A PDF or Word copy of one subject costs ₦1,000 for one term, or ₦2,000 for all three terms.

Sign in first, then choose your class and subject at checkout.