formulastreak

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.

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = $34.00
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusTotal
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped$34.00
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped$37.50
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped$44.00
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending$18.00
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped$38.00
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned$14.50
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped$24.00
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending$38.00
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped$34.00
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped$57.00
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped$36.25
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending$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.

Leaf & Loam orders · edit J2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultJ2 = $2.72
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusTotalTaxTax rate
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped$34.00$2.728%
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped$37.50$3.00
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped$44.00$3.52
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending$18.00$1.44
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped$38.00$3.04
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned$14.50$1.16
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped$24.00$1.92
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending$38.00$3.04
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped$34.00$2.72
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped$57.00$4.56
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped$36.25$2.90
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending$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.

Bulk price grid · edit B2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultB2 = $2.50
ABCDE
1Price \ Qty12510
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
Select J2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusTotalTaxTax rate
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped$34.008%
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped$37.50
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped$44.00
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending$18.00
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped$38.00
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned$14.50
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped$24.00
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending$38.00
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped$34.00
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped$57.00
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped$36.25
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending$22.00

Common mistakes

  • Locking with $ on the wrong part (e.g. K$2 when 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.

Practise it now