Absolute Reference in Excel: What the $ Sign Does
Why formulas break when you fill them down, and how the $ sign fixes it. Absolute references explained with share-of-total, tax rate and lookup examples.
By Ayoub Farid · · 2 min read
You write a perfect formula in row 2. You fill it down. Every row below shows #DIV/0! or nonsense. This is the most common Excel mistake there is, and the fix is two dollar signs.
What goes wrong
Each month's share of the yearly total:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Share |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| … | … | … | |
| 14 | Total | 67,800 |
In C2 you write =B2/B14. Correct: 6.2%. Fill it down to C13 and look at C3:
=B3/B15Excel moved both references down one row. B3 is right (Feb's sales), but B15 is empty, so you get #DIV/0!.
The fix: lock the total with $
=B2/$B$14Now fill down. C3 becomes =B3/$B$14, C4 =B4/$B$14… The dollar signs tell Excel: never move this one.
Relative vs absolute in one sentence
- Relative (
B2): "the cell in the same row as me". It moves when copied. - Absolute (
$B$14): "exactly B14". It never moves.
See relative vs absolute references for a side-by-side.
The shortcut: F4
While editing a formula, click on a reference and press F4 (Windows) or Cmd + T (Mac). It cycles:
B14 → $B$14 → B$14 → $B14 → B14
More in the F4 shortcut.
When to use $
Lock a reference when every row should point at the same single cell:
- A total:
=B2/$B$14 - A rate:
=B2*$F$1(tax, commission, exchange rate) - A target:
=IF(B2>=$F$2,"Hit","Miss") - A lookup table:
=VLOOKUP(A2,$H$2:$J$50,3,FALSE) - A condition range:
=COUNTIF($A$2:$A$100,A2)
The running total trick
Lock only the start of a range:
=SUM($B$2:B2)Filled down, it becomes SUM($B$2:B3), SUM($B$2:B4)… a growing range. See running total.
Locking just the row or column
Sometimes you need half a lock: B$1 (row fixed) or $A2 (column fixed). That's for tables you fill both down and across. See mixed references.
How to check your formula
After filling down, click the last cell and read the formula. The references you meant to lock should be identical to row 2. Everything else should have moved.
Practise it
"Lock with $" is a full lesson in Cellio's "Copy formulas right" unit. Fill-down exercises copy your formula down the column exactly like Excel, so when you forget the $, you see the broken copies and the feedback tells you which reference should have been locked. Then "Rates & totals" drills it with one rate applied to many rows.
Frequently asked questions
What does $ mean in an Excel formula?
It locks the column letter, the row number, or both, so that part of the reference doesn't change when the formula is copied.
How do I make a cell reference absolute?
Add $ before the column and row ($B$14), or click the reference and press F4.
When should I use absolute references?
When every copy of the formula must point at the same cell, like a total, a rate or a lookup table.
Does $ work the same in Google Sheets?
Yes. In Google Sheets on a computer, F4 also toggles the $ signs.