Absolute references in Excel
Last updated
An absolute reference such as $A$1 keeps pointing at the same cell when you copy a formula. A plain reference like A1 is relative: copied one row down it becomes A2. Put $ before the column, the row or both to lock them, or press F4 while editing the reference.
The four kinds of references
A1 $A$1 A$1 $A1
- A1
- Relative: column and row both move when you copy. Copied one row down,
=B2*2becomes=B3*2. - $A$1
- Absolute: neither the column nor the row moves. Use it for one cell that every row shares, such as a rate in
E1. - A$1
- Mixed, row locked: the column moves, row 1 stays. Useful for headers along the top of a grid.
- $A1
- Mixed, column locked: the row moves, column A stays. Useful for values down the left of a grid.
Practice absolute references here
Write the formula once, copy it down or across, and check it. Every copied cell is graded, so a missing $ shows up further down.
Lock the commission rate
Fill C2:C6 with each salesperson’s commission: sales times the rate in E1. Write the formula once in C2, lock the rate with $, then copy it down (Ctrl + D).
| Salesperson | Sales ($) | Commission ($) | Commission rate | 5% |
| Olivia Reed | 18,450.00 | |||
| James Carter | 23,900.00 | |||
| Sofia Patel | 15,275.00 | |||
| Liam Brooks | 31,200.00 | |||
| Emma Hughes | 9,800.00 |
How to lock a reference, step by step
- Type the formula for the first row, for example
=B2*E1. - Click on the reference that must not move (E1) in the formula bar, or put the cursor in it.
- Press F4: E1 becomes $E$1. Press it again for E$1, again for $E1, and once more to go back to E1.
- Press Enter, then copy the formula down (Ctrl + D) or across, and check a cell further down.
Absolute reference examples
One rate for every row
=B2*$E$1
Each row multiplies its own sales by the commission rate in E1.
Running total
=SUM($B$2:B2)
The start of the range stays on B2 and the end moves, so the range grows as you copy down.
Price grid
=$B2*C$1
Unit prices in column B, quantities in row 1: one formula fills the whole grid.
Three kinds at once
=$B2*C$1*(1-$I$1)
A grid with a discount in I1 that every cell uses.
Common problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Zeros further down | The shared cell wasn’t locked. Copied down, E1 became E2, E3… which are empty. | Add the $ signs: $E$1. Click a lower cell to check what the formula points at. |
| #VALUE! | A relative reference slid onto a header or a text cell when the formula was copied. | Lock the row or column that must stay put, for example C$1. |
| #REF! | The formula was copied up or left so far that a relative reference would leave the sheet. | Lock that reference with $, or copy from the first cell of the block. |
| F4 does nothing | On many laptops F4 controls the screen or the volume. | Press Fn + F4, or type the $ signs yourself. |
Relative, absolute or mixed?
Relative references (A1) suit values that change with each row or column, which is most of them. Absolute references ($A$1) suit one cell that every formula shares, like a rate, a target or a total. Mixed references (A$1, $A1) are for grids where one direction must move and the other must not. Copying a formula moves its relative parts; cutting and pasting it moves nothing, so the $ signs only matter when you copy or fill.
Frequently asked questions
What does the $ sign mean in an Excel formula?
It locks the part that follows it. In $A$1 both the column and the row are locked; in A$1 only the row; in $A1 only the column.
What is the shortcut for an absolute reference?
Put the cursor in the reference while editing and press F4. Each press cycles through $A$1, A$1, $A1 and back to A1.
When should I use a mixed reference?
When you copy a formula both down and across, as in a price grid: =$B2*C$1 keeps the prices in column B and the quantities in row 1.
Why does my formula work in the first row only?
A reference that every row needs moved when you copied the formula. Click a lower cell, look at the formula bar and lock that reference with $.
Keep practising
All 5 absolute reference exercises, with explanations and progress tracking.
Open the absolute references practice set