ITKokka
Lesson 7 ยท Note 11

Absolute Cell References

๐Ÿ“Œ The Short Note

Download

๐Ÿ“– Explanation

Absolute Cell References

Introduction

A spreadsheet formula can refer to values stored in other cells. When we copy a formula to another cell, the references may change automatically. Sometimes, however, we need a particular row, column, or cell to remain fixed.

An absolute cell reference uses the dollar sign ($) to keep the required part of a cell address unchanged when a formula is copied. This is useful when the same value, such as a multiplier, tax rate or unit price, must be used in many calculations.

This note explains the different ways to fix cell references and how to use them when copying formulas.

1. What Is an Absolute Cell Reference?

An absolute cell reference is a cell reference in which the column, the row, or both are fixed so that the reference does not change in the specified way when a formula is copied.

By default, a reference such as A1 is a relative reference. Its column and row can adjust when the formula is copied to another location.

The dollar sign ($) is used to fix a column or row:

  • $A$1 fixes both column A and row 1.
  • $A1 fixes column A, but allows the row number to change.
  • A$1 fixes row 1, but allows the column letter to change.

The three forms are often introduced as absolute cell, absolute column and absolute row references. The latter two are also known as mixed references, because only one part of the reference is fixed.

2. Absolute Column Reference ($A1)

An absolute column reference keeps the column letter fixed while allowing the row number to change.

In $A1:

  • $A means that column A is fixed.
  • 1 means that the row can change when the formula is copied vertically.

<ExampleLabel>{=html}Example: Keeping the column fixed while copying down</ExampleLabel>{=html}

Suppose column A contains numbers, column B contains multipliers, and column C is used to calculate the results.

The formula in cell C2 is:

=$A2*B2

When this formula is copied down, the references change as follows:

Destination cell Formula


C2 =$A2*B2 C3 =$A3*B3 C4 =$A4*B4 C5 =$A5*B5

The reference to column A remains fixed, while the row number changes to match each row. The relative reference B2 also adjusts as the formula is copied.

When is it useful?

Use $A1 when formulas may be copied to different rows but must continue referring to column A.

3. Absolute Row Reference (A$1)

An absolute row reference keeps the row number fixed while allowing the column letter to change.

In A$1:

  • A means that the column can change.
  • $1 means that row 1 is fixed.

<ExampleLabel>{=html}Example: Keeping the row fixed while copying across</ExampleLabel>{=html}

Suppose row 1 contains values and a formula uses a value from that row. If the formula contains =B$1*A2 and is copied to the right, the fixed row reference changes its column but keeps row 1.

Destination cell Formula


B2 =B$1*A2 C2 =C$1*A2 D2 =D$1*A2

The reference to row 1 remains fixed, while the column letter changes from B to C and then D.

When is it useful?

Use A$1 when a formula is copied across columns but must continue referring to the same row.

4. Absolute Row and Column Reference ($A$1)

A fully absolute cell reference keeps both the column letter and the row number fixed.

In $A$1:

  • $A fixes column A.
  • $1 fixes row 1.

Therefore, the reference always points to cell A1, even when the formula is copied to another row or column.

<ExampleLabel>{=html}Example: Using a fixed tax rate</ExampleLabel>{=html}

Imagine a worksheet where cell B1 contains a tax rate of 0.15 (15%). Product prices are listed in column B, and the tax amount is calculated in the next column.

The formula in cell C3 can be:

=B3*$B$1

Here:

  • B3 is a relative reference. It changes to B4, B5 and so on when copied down.
  • $B$1 is an absolute reference. It always points to the tax rate in cell B1.

When the formula is copied down, it becomes:

Destination cell Formula


C3 =B3*$B$1 C4 =B4*$B$1 C5 =B5*$B$1

The product price reference changes for each row, but the tax-rate reference remains fixed.

A total price including tax can be calculated with:

=B3*(1+$B$1)

This multiplies the product price by 1 plus the fixed tax rate.

When is it useful?

Fully absolute references are useful when many formulas need to use one fixed value, such as:

  • A tax rate.
  • A discount percentage.
  • A fixed multiplier.
  • An exchange rate.
  • A standard commission rate.

5. Applying Cell References in Calculations

Absolute references are especially useful when a worksheet contains many similar calculations and one input value must remain constant.

<ExampleLabel>{=html}Example: Calculating total prices for several items</ExampleLabel>{=html}

Suppose a worksheet contains item names, quantities and unit prices. The unit price for each item is stored in column C, and the quantity is in column B.

The total price can be calculated by multiplying the quantity by the unit price. If a fixed unit price or rate is stored in a separate cell, an absolute reference can be used to keep that cell fixed when the formula is copied.

For example, if the fixed rate is stored in $C$1, a formula can be written as:

=B2*$C$1

Copying the formula down changes B2 to B3, B4 and so on, while $C$1 remains unchanged.

The same method can be used for calculations involving tax rates, exchange rates and other fixed values.

6. Comparison of Absolute Reference Forms

Reference form What remains fixed? What can change?


$A$1 Column A and row 1 Neither part changes $A1 Column A Row number A$1 Row 1 Column letter

The position of the dollar sign is important:

  • A dollar sign before the column letter fixes the column.
  • A dollar sign before the row number fixes the row.
  • Dollar signs before both parts fix the entire cell reference.

How References Change When Copied

The following examples show how each reference behaves when a formula is copied one column to the right and one row down:

Original reference Copied one column right and one row down


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

Key Idea: $A$1 fixes both the column and row. $A1 fixes only the column, while A$1 fixes only the row.

7. Important Points for Examinations

  • The dollar sign ($) is used to fix a column, a row, or both in a cell reference.
  • $A$1 keeps both the column and row fixed.
  • $A1 keeps the column fixed, but the row can change.
  • A$1 keeps the row fixed, but the column can change.
  • An ordinary reference such as A1 is relative by default.
  • Absolute references are useful when copying formulas that must continue using a constant value.
  • A formula can combine relative and absolute references, for example =B3*$B$1.

Summary

Cell references determine which cells a spreadsheet formula uses. When formulas are copied, the dollar sign controls which parts of a reference remain fixed.

Remember these forms:

  • $A$1 --- both column and row are fixed.
  • $A1 --- column is fixed; row can change.
  • A$1 --- row is fixed; column can change.

Using the correct reference form makes it easier to copy formulas across rows and columns while keeping important values in the correct cells.

Share:
โ† Relative and Absolute Cell ReferencesWhy Use Charts? โ†’