Absolute vs Relative Cell References, When to Use the $ Sign
You write a formula in the first row, drag it down, and the first answer is right while every answer below it is zero or nonsense. Nine times out of ten, the cause is a reference that moved when it should have stayed put. The fix is a dollar sign, and knowing exactly where to put it takes about five minutes to learn.
1. Relative references move with the formula
Microsoft's documentation explains that a cell reference is relative by default, meaning it is relative to the location of the cell that holds the formula. If cell C2 refers to A2, Excel actually stores that as "two columns to the left, same row." Copy the formula somewhere else, and it keeps that relationship rather than the original address.
Microsoft's example: copy =B4*C4 from D4 down to D5, and it becomes =B5*C5. That is usually exactly what you want. A line-item total should multiply the price and quantity on its own row.
2. Absolute references stay fixed
When a formula should always point to one specific cell, such as a tax rate or exchange rate stored at the top of the sheet, you make the reference absolute by putting a dollar sign before both the column letter and the row number. Microsoft's example: copy =$B$4*$C$4 from D4 to D5, and it stays exactly the same.
3. A worked example: the tax rate that disappears
Here is a small order sheet. The tax rate, 8 percent, sits alone in F1.
| Item (A) | Price (B) | Qty (C) | Subtotal (D) | Tax (E) |
|---|---|---|---|---|
| Paper | 6.50 | 4 | 26.00 | 2.08 |
| Pens | 12.00 | 2 | 24.00 | 1.92 |
| Toner | 48.00 | 1 | 48.00 | 3.84 |
| Folders | 9.25 | 3 | 27.75 | 2.22 |
The subtotal in D2 is =B2*C2, filled down. Because both references are relative, D5 correctly becomes =B5*C5 and returns 27.75.
Now the tax column. Suppose you write this in E2 and fill it down:
=D2*F1
E2 shows 2.08, which is correct. But E3, E4 and E5 all show 0. Look at what Excel did with the copies: E3 now holds =D3*F2, and E5 holds =D5*F4. The reference to the tax rate slid down one row at a time into empty cells, and an empty cell counts as zero.
The fix is to lock F1:
=D2*$F$1
Filled down, E5 becomes =D5*$F$1, and the column shows 2.08, 1.92, 3.84 and 2.22 as in the table. The subtotal reference still moves, the rate stays fixed.
A quick way to spot this error: if only the first row of a filled column is right, check whether a reference that should be fixed is missing its dollar signs.
4. Mixed references lock only one part
Microsoft describes a third, less common type: the mixed reference, where the dollar sign sits before only the column or only the row. $B4 keeps column B fixed but lets the row change. C$4 keeps row 4 fixed but lets the column change.
Microsoft's documentation includes a table showing what happens when a formula in A1 is copied two cells down and two cells to the right, to C3:
| Reference in A1 | After copying to C3 |
|---|---|
| $A$1 | $A$1 (absolute) |
| A$1 | C$1 (mixed) |
| $A1 | $A3 (mixed) |
| A1 | C3 (relative) |
The classic use for mixed references is a grid where one input runs down the side and another runs across the top. Put 1, 2 and 3 in B7:D7, and 4, 5 and 6 in A8:A10. Then enter one formula in B8 and fill it across and down to D10:
=$A8*B$7
Every cell multiplies its own row's value in column A by its own column's value in row 7. D10 becomes =$A10*D$7 and returns 18 (6 times 3). C9 returns 10 (5 times 2).
Without the dollar signs, =A8*B7 filled the same way drifts. D10 would end up as =C10*D9, multiplying two other grid cells together and returning 180, a number that means nothing.
5. Switching reference types quickly
You do not need to type the dollar signs by hand. Microsoft's instructions for Excel on Windows: select the cell, click the reference in the formula bar, and press F4. Each press cycles through the four types: $A$1, A$1, $A1, then A1 again. In Excel for Mac, Microsoft's documentation gives Command+T for the same cycle.
6. A simple rule of thumb
Ask one question about each reference before you copy a formula: "When this formula moves, should this cell move with it?"
- Yes, in both directions: leave it relative (A1).
- No, never: make it absolute ($A$1). Rates, targets, totals and lookup tables usually fall here.
- Only across or only down: use a mixed reference, locking the part that must stay put.
7. Key takeaways
- References are relative by default and shift when the formula is copied.
- A dollar sign before the column and row ($F$1) keeps a reference fixed when copied.
- If only the first row of a filled column is right, a reference that should be absolute is probably relative.
- Mixed references ($A8 or B$7) lock only the column or only the row, which is ideal for grids.
- Press F4 on Windows, or Command+T on Mac, to cycle through reference types.