Learning Objectives
After completing this lesson, students will be able to:
- Understand the concept of cell referencing.
- Differentiate between Relative, Absolute and Mixed References.
- Use AutoFill correctly with different references.
- Apply referencing while performing calculations.
- Create formulas using appropriate references.
Prerequisite Knowledge
- Rows, columns and cell addresses.
- Spreadsheet formulas and functions.
- Using AutoFill.
What is Cell Referencing?
A Cell Reference is the address of a cell that is used in a formula. Instead of typing numbers directly into a formula, spreadsheets use cell references so that calculations update automatically whenever the data changes.
Example:
=B2+C2+D2
Here, B2, C2 and D2 are cell references.
Types of Cell References
| Reference Type | Notation | Description |
|---|---|---|
| Relative Reference | A1 |
Changes automatically when copied. |
| Absolute Reference | $A$1 |
Row and column remain fixed. |
| Mixed Reference | $A1 or A$1 |
Either the row or the column remains fixed. |
1. Relative Reference
A relative reference changes automatically when a formula is copied to another cell.
| Original Formula | Copied Down One Row |
|---|---|
=B2+C2 |
=B3+C3 |
Relative references are used when the same calculation is required for multiple rows or columns.
2. Absolute Reference
An absolute reference always points to the same cell, even when the formula is copied.
| Original Formula | Copied Formula |
|---|---|
=B2*$F$1 |
=B3*$F$1 |
The dollar sign ($) locks both the row and the column.
3. Mixed Reference
| Reference | Meaning |
|---|---|
$A1 |
Column A remains fixed while the row changes. |
A$1 |
Row 1 remains fixed while the column changes. |
Mixed references are useful when copying formulas across both rows and columns.
Comparison of References
| Reference | Column Fixed | Row Fixed |
|---|---|---|
A1 |
❌ | ❌ |
$A$1 |
✔ | ✔ |
$A1 |
✔ | ❌ |
A$1 |
❌ | ✔ |
Example: GST Calculator
Suppose the GST rate (18%) is stored in cell F1.
| Item | Price | GST |
|---|---|---|
| Notebook | 100 | =B2*$F$1 |
| Pen | 50 | =B3*$F$1 |
Using $F$1 ensures that the GST rate remains fixed while copying the formula.
Using AutoFill with References
- Enter the formula in the first row.
- Select the cell.
- Drag the Fill Handle downward.
- Observe how relative references change automatically.
- Notice that absolute references remain unchanged.
Classroom Activity
- Create a worksheet containing the prices of five products.
- Store the GST rate in a separate cell.
- Use an absolute reference to calculate GST.
- Use AutoFill to copy the formula for all products.
- Observe the difference between relative and absolute references.
Practical Activity
| Product | Price | GST Rate | GST Amount |
|---|---|---|---|
| Notebook | 100 | 18% | ? |
| Pen | 50 | 18% | ? |
| Pencil | 20 | 18% | ? |
| Eraser | 15 | 18% | ? |
| Geometry Box | 250 | 18% | ? |
Use $F$1 (or another appropriate cell containing the GST rate) as an absolute reference.
Points to Remember
- Every formula uses cell references.
- Relative references change automatically when copied.
- Absolute references remain fixed.
- Mixed references fix either the row or the column.
- Choose the correct reference according to the calculation.
Quick Check
- What is a cell reference?
- What is the difference between relative and absolute references?
- Which symbol is used to create an absolute reference?
- What does
$A1represent? - What does
A$1represent? - Why is absolute referencing useful in GST calculations?
Homework
- Create a worksheet containing the prices of ten products.
- Store the GST rate (18%) in a separate cell.
- Calculate the GST amount for every product using an absolute reference.
- Write two examples each of:
- Relative References
- Absolute References
- Mixed References
- Save the file as GST_Calculator.ods.
Assessment
| Assessment Method | Description |
|---|---|
| Oral Questioning | Types of cell references and their uses. |
| Worksheet | Create formulas using appropriate references. |
| Practical Observation | Teacher observes correct use of relative, absolute and mixed references. |
Summary
In this session, you learned how cell references make spreadsheet calculations dynamic and efficient. You explored the three types of references—relative, absolute and mixed—and applied them while creating formulas. Understanding referencing is essential for building accurate and reusable spreadsheets.