Absolute vs relative references: when to use $ in Excel
A relative reference moves when you copy a formula; an absolute one stays. Worked examples of A1, $A$1, $A1 and A$1, and the F4 key that switches them.
Use a dollar sign when a reference must stay on the same cell as you copy the formula. A relative reference like A1 moves with the copy. An absolute reference like $A$1 does not.
That is the whole difference. The dollar sign does not change the answer in the cell you type it in. It only changes what happens when you copy or fill the formula to other cells.
What a relative reference really means
Type =B2*C2 in D2. Excel does not remember "B2 and C2". It
remembers "the cell two to the left and the cell one to the left".
Fill the formula down to D3 and it still means that. So D3 holds
=B3*C3. This is why one formula can work for 200 rows. Every
reference you type is relative unless you add a dollar sign.
When a reference must not move
Here is a commission sheet. Every person gets 5% of their sales. The rate is in one cell, E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Name | Sales | Commission | Rate | |
| 2 | Ana | 4200 | 210 | 5% | |
| 3 | Ben | 3900 | 195 | ||
| 4 | Cleo | 5100 | 255 | ||
| 5 | Dev | 2800 | 140 |
In C2 you type =B2*E2. It gives 210, which is right. Then you
fill it down to C5.
C3 now holds =B3*E3. E3 is empty, and an empty cell counts as
0. So Ben's commission is 0. So are Cleo's and Dev's.
Fix: lock the rate. Type =B2*$E$2 in C2 and
fill down. C3 holds =B3*$E$2 and gives 195. C4 gives 255 and C5
gives 140.
There is no error message here. A column of zeros, or numbers that are too small, looks normal. After you fill a formula, click the last cell and read what it points at.
The four kinds of reference
A dollar sign locks the part that comes right after it. $A locks the column. $1 locks the row. You can lock one, both or neither.
| You type | Fill 1 row down | Fill 1 column right | Name |
|---|---|---|---|
| A1 | A2 | B1 | Relative |
| $A$1 | $A$1 | $A$1 | Absolute |
| $A1 | $A2 | $A1 | Mixed: column locked |
| A$1 | A$1 | B$1 | Mixed: row locked |
To read one, cover the dollar sign with your finger. The letter or number under it cannot move. Everything else can.
Mixed references: one formula for a whole block
Mixed references matter when you fill one formula both down and across. Here the prices go down column A. The discounts go across row 1.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Price | 5% | 10% | 20% |
| 2 | 40 | 38 | 36 | 32 |
| 3 | 75 | 71.25 | 67.5 | 60 |
| 4 | 120 | 114 | 108 | 96 |
Each cell needs the price from its own row and the discount from its own column. The price is always in column A. The discount is always in row 1.
In B2, type =$A2*(1-B$1). It gives 38. Fill it right to D2,
then down to row 4.
$A2 keeps column A and lets the row move. B$1 keeps row 1 and lets the column move. D4 holds
=$A4*(1-D$1) and gives 96. That is 120 with 20% off.
To check a block, do not read the first cell. Read the opposite corner, here D4. If the first cell and the opposite corner are both right, the cells between them are right too.
Lock everything, as in =$A$2*(1-$B$1), and every cell shows 38.
That is the other common mistake. It feels safe, but the formula cannot move at all.
F4 adds the dollar signs for you
On Windows, edit the formula and click inside a reference. Then press F4. Each press changes the reference to the next kind:
- A1 becomes $A$1
- $A$1 becomes A$1
- A$1 becomes $A1
- $A1 goes back to A1
On some laptops you press Fn+F4. On a Mac, the shortcut is different.
How to decide, every time
Ask one question about each reference: when I copy this formula, should this move?
- A rate, a tax or a total: one cell used by every row. Lock it with $.
- The value on this row: it should move with the row. Leave it relative.
- A block with labels on the top and the side: lock the side label's column. Lock the top label's row.
A share of a total is a good example. The total needs dollar signs, and each part does not.
What a dollar sign does not do
A dollar sign stops a reference moving when you copy. It does not stop Excel from updating the reference when the sheet changes.
Insert a row above row 2 and the rate moves down to E3. Every formula now says $E$3. That is what you want, because the formulas still point at the rate.
Cut and paste is different from copy. Cut a formula and paste it, and no reference changes. That is true with or without dollar signs.
A relative reference can also break. Copy =A2+B2 from C2 into
A2, and there is no cell two to the left. You get #REF!. The article on the
#REF! error explains that case.
Questions people ask
Does a dollar sign change the result?
No. =B2*E2 and =B2*$E$2 give the
same answer in C2. They only give different answers after you copy them.
Can I lock a whole column?
Yes. A:A becomes B:B when you fill right. $A:$A stays on column A. This is useful for a lookup range that many formulas share.
The fastest way to learn this is to fill a formula and look at the copy. Each lesson is a real sheet you type into, and each answer is checked when you press Enter. See the Excel practice page to start.