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
Key formulas
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. 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. 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 WeLearnTeach 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.
