Absolute vs Relative Cell References in Excel and Google Sheets
Basics4 minInnovialabs editorial team
In short
The $ sign locks part of an address: $A$1 never changes when copied, A1 shifts fully, $A1 locks the column and A$1 locks the row; press F4 to cycle through them.
The short answer: what the $ sign does
When you copy a formula to another cell, Excel and Google Sheets adjust its addresses relative to the new position. A $ sign stops the part of the address it stands in front of from changing.
| Reference | What is locked | Copied down | Copied right |
|---|---|---|---|
A1 | nothing | A2, A3… | B1, C1… |
$A$1 | column and row | $A$1 | $A$1 |
$A1 | column only | $A2, $A3… | $A1 |
A$1 | row only | A$1 | B$1, C$1… |
- A relative reference (
A1) is the default. Use it when the same calculation repeats row by row. - An absolute reference (
$A$1) points to one specific cell: an exchange rate, a tax rate, a discount. - A mixed reference (
$A1orA$1) is for formulas you copy both down and across.
Example 1: relative reference copied down
Column B holds quantity, column C holds price. In D2 you calculate the total:
=B2*C2
Drag it down and D3 becomes =B3*C3, D4 becomes =B4*C4. That is a relative reference: the formula remembers an offset ("two cells to the left times one cell to the left"), not a fixed address.
Example 2: absolute reference to a constant
The exchange rate sits in F1 and you need to convert the totals in column D. If you type =D2*F1 in E2 and drag it down, E3 gets =D3*F2. F2 is empty, so the result turns into zero.
The correct version:
=D2*$F$1
Now only D2 changes as you copy, and the rate always comes from F1.
Example 3: mixed references in a grid
The classic task: numbers in column A (A2:A10), numbers in row 1 (B1:J1), and one formula in B2 that you can fill across the whole block.
=$A2*B$1
$A2locks column A while the row changes, so the first factor always comes from the first column.B$1locks row 1 while the column changes, so the second factor always comes from the first row.
The same trick solves real tasks: a monthly plan multiplied by regional coefficients, or prices multiplied by markups for different categories.
The F4 shortcut
You don't have to type $ by hand. Put the cursor on an address in the formula bar and press F4 to cycle through the options:
A1 → $A$1 → A$1 → $A1 → A1
- It works in Excel for Windows and in Google Sheets. On many laptops you need Fn + F4.
- In Excel for Mac the shortcut is usually Cmd + T or Fn + F4, depending on keyboard settings.
- If you select a whole range like
A1:B5in the formula, F4 locks both ends.
Common mistakes
- A constant without $. The most frequent one: the reference to a rate or total drifts as you drag, and half the column is calculated from empty cells.
- An extra $. You locked
$B$2where a row-by-row reference was needed, so every row shows the same result. - The wrong part locked.
A$1instead of$A1works when copied down but breaks when copied right. Always check the formula in the far corner of the range. - A lookup range without $.
=VLOOKUP(A2, Products!A2:C100, 3, FALSE)turns intoA3:C101when copied down, and the top rows of the lookup table stop being found. UseProducts!$A$2:$C$100. - Cut instead of copy. When you cut (Ctrl+X) and paste, the formula moves without adjusting its addresses. That is not a bug, but the behaviour differs from copying, so keep it in mind.
How to check formulas quickly
- Click a cell at the end of the range and read the formula: references should point where you expect.
- Show all formulas: in Excel press Ctrl + the grave accent key (left of 1), in Google Sheets use View → Show → Formulas.
- In Excel, use Trace Precedents on the Formulas tab: arrows show where a formula takes its data from.
An alternative: named ranges and tables
Instead of $F$1 you can name the cell, for example Rate, and write =D2*Rate. A name always points to the same cell and makes the formula easier to read. In Excel, tables (Ctrl+T) also help: formulas inside them use column names and fill automatically, so you think about $ far less often.