Cell Referencing (Cambridge International IGCSE ICT)

Relative

What is an relative cell reference?

• A relative cell reference means that when a formula is copied the cell reference can change
• The reference is relative to the cell containing the formula
• It is the default cell referencing used in spreadsheets

Example

• Consider the following example spreadsheet:

• When you copy a formula that includes a relative cell reference, spreadsheet software adjusts the reference relative to the new location
• For example, if you copy the formula "=A1+B1" from cell C1 to C2, the formula will adjust to "=A2+B2"

What are the benefits of relative cell references?

• Relative cell references saves time by automatically adjusting references when being copied, improving efficiency
• Relative cell references are flexible and allow users to create formulas that can be used on a range of data

Absolute

What is an absolute cell reference?

• An absolute cell reference means that when a formula is copied the cell reference does not change
• Absolute cell references are fixed using the $ symbol

Example

• Consider the following example spreadsheet:

• If you input the formula "=A1+B1" in cell C1 and drag the fill handle down to copy the formula to cell C2, the formula in C2 will change to "=A2+B2"
• But if you input the formula "=$A$1+B1 " in cell C1 and drag the fill handle down, the formula in C2 will still refer to cell A1: "=$A$1+B2**"

What are the benefits of absolute cell references?

• Absolute cell reference provide consistency in a spreadsheet, for example:

  • Calculations using a tax rate (fixed) that you want consistently applied across the spreadsheet

• Absolute cell references provide clarity in formulas, they become easier to understand