WeLearn

Grade 10 Information Technology · Lesson 3.2

Spreadsheets

Unit 3: Application Software

Grade 10 spreadsheet work builds on cells, formulas and basic functions. An important new idea is cell referencing. A relative reference such as A1 changes when a formula is copied: copying =B2*C2 down one row gives =B3*C3. An absolute reference such as $A$1 stays fixed, which is needed when every row must use one constant, like a tax rate in a single cell. Mixed references ($A1 or A$1) fix only the column or the row.

Key terms

Relative reference
changes when copied (A1)
Absolute reference
stays fixed when copied ($A$1)
IF function
returns one of two results depending on a condition
COUNTIF/SUMIF
count or add cells meeting a condition
Conditional formatting
automatic formatting based on values
Data validation
rules restricting entries
Filter
show only rows that match criteria
All Grade 10 Information Technology key terms →

Key formulas

Gives Pass or Fail depending on the mark in D2.
Multiplies each row by a fixed value in F1 (absolute reference).

Summary

  • Relative references change when copied; absolute ($) references do not.
  • IF, COUNTIF, SUMIF and VLOOKUP add logic and lookup power.
  • Sort, filter, conditional formatting, validation and charts manage and present data.

In everyday life: Small traders in Merkato increasingly track stock and VAT in spreadsheets so their records match what the Ministry of Revenues requires.

Check yourself

  • What is the difference between A1 and $A$1?
  • Write an IF formula that shows 'Adult' if age in B2 is 18 or more.
  • What does #NAME? mean?

Practice questions

  1. 1. In a spreadsheet, which reference stays fixed when the formula is copied to other cells?

    • A. A1
    • B. $A$1
    • C. A$
    • D. 1A
    Show answer

    Answer: $A$1

    Dollar signs lock the column and row, making an absolute reference. A1 is relative and changes as it is copied.

  2. 2. Cells B1:B5 contain 12, 7, 20, 3 and 8. What does =MAX(B1:B5) return?

    Show answer

    Answer: 20

    MAX returns the largest value in the range, which is 20.

On WeLearn

The full lesson: reading notes, worked examples, class activities and the Unit 3 quiz

Study Spreadsheets on WeLearn

Teach Spreadsheets with WeLearn

We will bring a demo to your school: a lesson on your interactive TV, a virtual lab and a classroom game, with your own teachers.