Absolute Reference in Google Sheets: Using $ and F4
How absolute and mixed references work in Google Sheets, the $ sign, F4 and Fn+F4 shortcuts, and examples for totals, rates and running totals.
By Ayoub Farid · · 1 min read
Absolute references in Google Sheets work exactly as in Excel: a $ sign stops part of a reference from changing when you copy the formula.
The problem
Share of total, with the total in B14:
- C2:
=B2/B14✓ - C3 (after filling down):
=B3/B15✗ →#DIV/0!
The fix
=B2/$B$14Now every copy divides by B14.
The four forms
| Reference | Column | Row |
|---|---|---|
B14 | moves | moves |
$B$14 | fixed | fixed |
B$14 | moves | fixed |
$B14 | fixed | moves |
See mixed references.
The shortcut
Click on a reference while editing and press F4 (Windows/Chromebook) or Fn + F4 on many laptops and Macs. Each press cycles through the four forms. See F4 shortcut.
Common uses
- Rate:
=B2*$F$1 - Lookup table:
=VLOOKUP(A2,$H$2:$J$50,3,FALSE) - Running total:
=SUM($B$2:B2). See running total - Duplicate flag:
=COUNTIF($A$2:$A$500,A2)>1
Note: ARRAYFORMULA doesn't need it
With ARRAYFORMULA, there's no filling down, so you rarely need $. But in normal filled formulas, you do.
Practise it
Cellio's "Lock with $" lesson trains exactly this, and the skill is identical in Sheets and Excel. Full explanation in absolute references.
Frequently asked questions
How do I make an absolute reference in Google Sheets?
Add $ before the column letter and row number, e.g. $B$14, or press F4 while editing.
Why doesn't F4 work in Google Sheets?
Your keyboard may need Fn+F4, or the browser is intercepting the key.
Is $ the same in Google Sheets and Excel?
Yes, exactly the same.
What is a mixed reference in Google Sheets?
A reference with only the row or column locked, like B$1 or $A2.