Absolute and relative references
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
A $ locks the part of a reference after it so it doesn't move when the formula is copied: $K$2 is fully fixed, $A2 locks the column, B$1 locks the row. Press F4 while editing a reference to cycle through them.
When you copy a formula, its references move with it. That's usually what you want, until one of them shouldn't move. The $ sign is how you say so.
Relative references move
Fill =F2*G2 down one row and it becomes =F3*G3. Each reference shifts by however far the formula moved. Click any cell in the Total column to see its formula.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | $37.50 |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | $44.00 |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | $18.00 |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | $38.00 |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | $14.50 |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | $24.00 |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | $38.00 |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | $57.00 |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | $36.25 |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | $22.00 |
Absolute references stay put
Every order uses the same tax rate in K2. Without $, the copy in J3 would look at K3, which is empty, and show $0.00. $K$2 locks both the column and the row. Try deleting the dollar signs.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total | Tax | Tax rate |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | $2.72 | 8% |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | $37.50 | $3.00 | |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | $44.00 | $3.52 | |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | $18.00 | $1.44 | |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | $38.00 | $3.04 | |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | $14.50 | $1.16 | |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | $24.00 | $1.92 | |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | $38.00 | $3.04 | |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | $2.72 | |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | $57.00 | $4.56 | |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | $36.25 | $2.90 | |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | $22.00 | $1.76 |
Mixed references: lock one part
$A2 locks the column but lets the row move; B$1 locks the row. That's exactly what a grid needs: prices down column A, quantities across row 1, one formula for every cell.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Price \ Qty | 1 | 2 | 5 | 10 |
| 2 | $2.50 | $2.50 | $5.00 | $12.50 | $25.00 |
| 3 | $4.00 | $4.00 | $8.00 | $20.00 | $40.00 |
| 4 | $7.25 | $7.25 | $14.50 | $36.25 | $72.50 |
| 5 | $12.00 | $12.00 | $24.00 | $60.00 | $120.00 |
The F4 shortcut
While editing a reference, press F4 in Excel for Windows (Command+T in Excel for Mac) to cycle through K2 → $K$2 → K$2 → $K2. In Google Sheets it's F4 too (Fn+F4 on a Mac).
Check yourself
One real exercise from the “Cells and formulas” unit. Checked on our server; no account needed.Work out each order's tax in J2: its Total times the tax rate in K2. Lock the rate so it survives being filled down to J13.
- No typed numbers
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total | Tax | Tax rate |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | 8% | |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | $37.50 | ||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | $44.00 | ||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | $18.00 | ||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | $38.00 | ||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | $14.50 | ||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | $24.00 | ||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | $38.00 | ||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | ||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | $57.00 | ||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | $36.25 | ||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | $22.00 |
Common mistakes
Locking with
$on the wrong part (e.g.K$2when you fill across columns), so the reference still slides.Locking everything out of habit, then wondering why the filled copies all show the same answer.
Typing the rate into the formula (
=I2*0.08) instead of referencing a cell. When the rate changes, every formula needs editing.
Questions
What does the $ sign mean in Excel and Google Sheets?+
It locks the column letter or row number after it, so that part doesn't change when you copy the formula. `$A$1` is fully locked.
Do I need $ if I'm not copying the formula?+
No. References only shift when a formula is copied or filled. Adding $ is a good habit for any cell that's a fixed setting, like a rate.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.