ICT 0417 - Spreadsheets: Absolute and Relative Referencing

Objective

Know and understand the characteristics and use of absolute and relative cell referencing.

Introduction

In spreadsheet software like Microsoft Excel or Google Sheets, cell references are used to identify specific cells within the spreadsheet. Understanding the difference between relative and absolute cell references is crucial for creating dynamic and flexible spreadsheets. These references determine how a formula changes when the formula is copied to other cells.

Relative Cell Referencing

Definition: A relative cell reference is a reference that changes when a formula is copied to a different cell. The reference adjusts based on the new cell's position relative to the original cell.

Example: If cell A1 contains the value 10 and you enter the formula `=A1+5` in cell A2, the value in A2 will be 15. If you copy the formula to cell A3, the formula in A3 will automatically change to `=A3+5` (because it's relative).

How it works: The relative reference adjusts the column and row numbers based on the new location of the formula. For example, if the formula is in A1 and copied to A2, the column remains 'A' and the row number changes from '1' to '2'.

• The column letter remains the same.
• The row number changes.

Absolute Cell Referencing

Definition: An absolute cell reference is a reference that does not change when a formula is copied to a different cell. It remains fixed to the original cell.

Example: If cell A1 contains the value 10 and you enter the formula `=$A$1+5` in cell A2, the value in A2 will always be 15, regardless of where the formula is copied. The `$` signs indicate absolute referencing.

How it works: Absolute references use the dollar sign ($) before the column letter and/or row number. `$A$1` is an absolute reference to cell A1. `$A1` is an absolute reference to the column A, but the row number 1 is relative. `A$1` is a relative reference to the column A, but the row number 1 is absolute.

• The column letter remains the same.
• The row number remains the same.

Mixed Referencing

It's common to use a combination of relative and absolute references within a single formula. For example, `=$A$1` keeps the column fixed to A, but allows the row to change when the formula is copied. This is useful when you want to refer to a specific row while allowing the column to be flexible.

Table: Relative and Absolute Referencing Examples

Formula	Value in A1	Value in A2 (Relative)	Value in A2 (Absolute)
=A1+5	10	15	15
=$A$1+5	10	15	15
=A1+$A$1	10	20	20
=$A$1+A1	10	20	20

Use Cases

• Calculating totals: Using absolute references for the values you want to sum (e.g., prices) and relative references for the row numbers of the items being summed.
• Creating headers that remain fixed: Using absolute references for column headers so they stay in the same position when scrolling through the spreadsheet.
• Dynamic calculations: Using a combination of relative and absolute references to create formulas that adapt to different data arrangements.

Practice

Practice creating spreadsheets with different formulas using relative and absolute references. Experiment with copying formulas to see how the references change.