Locking Cells with Dollar Signs ($A$1)
Understand the difference between relative cells and absolute ($) locked cells. Lock master tax rates and currency values so your formulas never break when copied.
Why This Feature Matters
When you write a formula like =B2*E1 and copy it down to row 3, Excel naturally shifts the formula to =B3*E2. This automatic shifting is called a Relative Reference, and it is usually very helpful.
However, if your exchange rate or tax rate sits in a single cell (like E1), copying the formula down causes it to look at empty cells (E2, E3), returning zeroes or errors! To fix this, you "lock" the cell using dollar signs ($E$1), turning it into an Absolute Reference.
How the Dollar Sign ($) Works
Think of the dollar sign $ as a padlock that freezes a row or column in place:
| Reference | Type | What Happens When Copied |
|---|---|---|
B2 |
Relative (No lock) | Both column B and row 2 change freely as you copy across or down. |
$E$1 |
Absolute (Fully locked) | Both column E and row 1 stay permanently frozen on cell E1. |
$A2 |
Column locked only | Column A stays frozen, but the row number changes as you copy down. |
A$1 |
Row locked only | Row 1 stays frozen, but the column letter changes as you copy across. |
Step-by-Step Practice Guide
In this exercise, you will convert four product prices from USD to EUR using the fixed exchange rate stored in cell $E$1 (0.92):
Practice 1 — Convert Laptop Pro to EUR (Row 2)
In cell C2, multiply the USD price in B2 by the locked rate in $E$1.
=B2*$E$1In cell C2, calculate the EUR Price for Laptop Pro by multiplying USD Price (B2) by the fixed Exchange Rate in $E$1. Formula: =B2*$E$1.
Practice 2 — Convert Wireless Mouse to EUR (Row 3)
In cell C3, multiply the USD price in B3 by the locked rate in $E$1.
=B3*$E$1In cell C3, calculate the EUR Price for Wireless Mouse using the locked exchange rate in $E$1. Formula: =B3*$E$1.
Practice 3 — Convert 4K Monitor to EUR (Row 4)
In cell C4, multiply the USD price in B4 by the locked rate in $E$1.
=B4*$E$1In cell C4, calculate the EUR Price for 4K Monitor using the locked exchange rate in $E$1. Formula: =B4*$E$1.
Practice 4 — Convert Mechanical Keyboard to EUR (Row 5)
In cell C5, multiply the USD price in B5 by the locked rate in $E$1.
=B5*$E$1In cell C5, calculate the EUR Price for Mechanical Keyboard using the locked exchange rate in $E$1. Formula: =B5*$E$1.
You can quickly add dollar signs while typing a formula by pressing the F4 key (or Fn + F4 on laptops) on your keyboard after clicking a cell coordinate.
Share This Lesson
Spread the word with your peers and challenge your friends