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$1fixes both column A and row 1.$A1fixes column A, but allows the row number to change.A$1fixes 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:
$Ameans that column A is fixed.1means 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*B2When 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:
Ameans that the column can change.$1means 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:
$Afixes column A.$1fixes 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$1Here:
B3is a relative reference. It changes toB4,B5and so on when copied down.$B$1is an absolute reference. It always points to the tax rate in cellB1.
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$1Copying 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$1keeps both the column and row fixed.$A1keeps the column fixed, but the row can change.A$1keeps the row fixed, but the column can change.- An ordinary reference such as
A1is 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.