ITKokka
Lesson 7 ยท Note 10

Relative and Absolute Cell References

๐Ÿ“Œ The Short Note

Download

๐Ÿ“– Explanation

Relative and Absolute Cell References

Introduction

Spreadsheet formulas often use values stored in other cells. Instead of typing the same numbers into every formula, we can refer to cells by their addresses, such as A1, B2 or $C$5.

When a formula is copied to another cell, its references may change or remain fixed. This behaviour depends on the type of cell reference used. Understanding relative, absolute and mixed references helps us copy formulas accurately and avoid calculation errors.

This note explains how formulas are copied, how relative and absolute references behave, and when each reference type is useful.

1. Copying Formulas in a Worksheet

A formula can be copied from one cell to another so that the same calculation can be performed using different rows or columns of data.

Common ways to copy a formula include:

  • Copy and Paste: Select the cell containing the formula, copy it, select the destination cell and paste it. Common keyboard shortcuts are Ctrl + C and Ctrl + V.
  • Fill Handle: Select the cell containing the formula and drag the small square at the bottom-right corner of the selected cell across or down to the required cells.
  • Copying across rows or columns: A formula can be copied horizontally or vertically. Spreadsheet software adjusts relative references according to the new position of the formula.

When a formula is copied, the result depends on whether its references are relative, absolute or mixed.

<ExampleLabel>{=html}Example: Copying a formula down a column</ExampleLabel>{=html}

Suppose a worksheet contains:

Cell Number 1 (A) Number 2 (B) Formula / Result (C)


Row 2 5 3 =A2+B2 Row 3 10 4 =A3+B3 Row 4 6 7 =A4+B4 Row 5 20 5 =A5+B5

If the formula =A2+B2 in cell C2 is copied down, the row numbers in the references increase to match each destination row.

The formula in C3 becomes =A3+B3, and the formula in C4 becomes =A4+B4.

2. Relative Cell References

A relative cell reference is a cell reference that changes automatically when a formula is copied to another cell.

References such as A1, B2 and C5 are relative references by default in common spreadsheet applications.

The reference adjusts according to the movement of the copied formula:

  • When a formula is copied down, relative row references change.
  • When a formula is copied up, relative row references decrease.
  • When a formula is copied right, relative column references change.
  • When a formula is copied left, relative column references decrease.

The spreadsheet preserves the relative position between the formula cell and the referenced cell.

<ExampleLabel>{=html}Example: Copying a formula down</ExampleLabel>{=html}

Assume cell C2 contains:

=A2+B2

When this formula is copied down one row to C3, it changes to:

=A3+B3

When copied down again to C4, it becomes:

=A4+B4

The row references change because the formula has moved down the worksheet.

<ExampleLabel>{=html}Example: Copying a formula across a row</ExampleLabel>{=html}

Suppose cell A2 contains:

=A1*2

When the formula is copied to the right:

Destination cell Adjusted formula


A2 =A1*2 B2 =B1*2 C2 =C1*2 D2 =D1*2

The column reference changes from A to B, then C and D.

Key Idea: Relative references adjust their row and column parts when a formula is copied. This makes them useful when the same calculation must be repeated for different records.

3. Absolute Cell References

An absolute cell reference is a cell reference that remains fixed when a formula is copied to another cell.

The dollar sign ($) is used to lock both the column and the row.

For example:

  • $A$1 --- column A and row 1 are both fixed.
  • $B$5 --- column B and row 5 are both fixed.

When a formula containing an absolute reference is copied, that reference continues to point to the same cell.

<ExampleLabel>{=html}Example: Using an absolute reference for a fixed multiplier</ExampleLabel>{=html}

Imagine a worksheet in which column A contains product values and cell B1 contains a fixed multiplier of 2.

A formula in C2 can be written as:

=A2*$B$1

Here:

  • A2 is a relative reference. It changes when the formula is copied to another row.
  • $B$1 is an absolute reference. It always refers to cell B1.

When the formula is copied down, the formulas become:

Destination cell Formula


C2 =A2*$B$1 C3 =A3*$B$1 C4 =A4*$B$1 C5 =A5*$B$1

The product reference changes for each row, but the multiplier remains fixed at B1.

Uses of Absolute References

Absolute references are useful when a formula must repeatedly use a value stored in one fixed cell.

Examples include:

  • Applying the same tax rate to many products.
  • Using a fixed discount rate for a list of prices.
  • Converting several values using one fixed exchange rate.
  • Applying a constant multiplier to multiple records.

If the fixed value changes, the formulas using that absolute reference recalculate automatically.

4. Mixed Cell References

A mixed cell reference fixes either the column or the row, but not both.

There are two types:

Reference What is fixed? What can change?


$A1 Column A Row number A$1 Row 1 Column letter

  • $A1: The dollar sign before A locks the column. The row number can change when the formula is copied vertically.
  • A$1: The dollar sign before 1 locks the row. The column letter can change when the formula is copied horizontally.

<ExampleLabel>{=html}Example: Mixed references</ExampleLabel>{=html}

If a formula contains $A1 and is copied down, the reference changes to $A2, $A3 and so on. The column remains A.

If a formula contains A$1 and is copied to the right, the reference changes to B$1, C$1 and so on. The row remains 1.

Mixed references are useful in tables where formulas are copied in more than one direction and only one part of a reference should remain fixed.

5. Comparison of Cell Reference Types

Reference type Example Behaviour when copied


Relative A1 Both column and row can change Absolute $A$1 Neither column nor row changes Mixed -- fixed column $A1 Column stays fixed; row can change Mixed -- fixed row A$1 Row stays fixed; column can change

The dollar sign indicates which part of the reference is fixed.

Understanding the Difference

Consider copying a formula one column to the right and one row down. The reference parts behave as follows:

Original reference New reference


A1 B2 $A$1 $A$1 $A1 $A2 A$1 B$1

This comparison is useful when answering questions about how formulas change after copying.

6. Practical Example: Calculating a Price with a Fixed Tax Rate

A shop may store product prices in column A and a tax rate in cell B1. The tax amount for each product can be calculated by multiplying its price by the tax rate.

If the first product price is in A2 and the tax rate is stored in B1, enter this formula in C2:

=A2*$B$1

Then copy the formula down the column.

  • A2 changes to A3, A4 and so on.
  • $B$1 remains unchanged for every product.

This combines a relative reference and an absolute reference in one formula. The same approach can be used for a fixed discount, commission or exchange rate.

7. Important Points for Examinations

  • Formulas can be copied using Copy and Paste or the Fill Handle.
  • A cell reference such as A1 is a relative reference by default.
  • A relative reference changes when the formula is copied.
  • An absolute reference uses $ before both the column letter and row number, for example $A$1.
  • An absolute reference does not change when the formula is copied.
  • A mixed reference fixes either the column or the row.
  • $A1 fixes the column, while A$1 fixes the row.
  • A formula can contain a combination of relative, absolute and mixed references.

Key Idea: Use a relative reference when the referenced cell should move with the copied formula. Use an absolute reference when the formula must always refer to the same cell. Use a mixed reference when only the row or column should remain fixed.

Summary

Cell references allow spreadsheet formulas to use values stored in other cells. When formulas are copied, relative references adjust automatically, while absolute references remain fixed. Mixed references lock either the column or the row.

Remember these four examples:

  • A1 --- relative reference.
  • $A$1 --- absolute reference.
  • $A1 --- fixed column, changing row.
  • A$1 --- changing column, fixed row.

Choosing the correct reference type makes it easier to copy formulas across a worksheet while keeping calculations accurate.

Share:
โ† Formatting a WorksheetAbsolute Cell References โ†’