Locking a Single Cell With Absolute References

A very common need is to point lots of formulas at one shared value: a tax rate, an exchange rate, a target, or a fixed fee.

That single value lives in one cell, and every other formula must keep referring back to it no matter where it is copied. An absolute reference makes that possible.

An absolute reference uses a dollar sign before both the column and the row, like $A$1. It is fully locked.

Copy a formula containing $A$1 anywhere on the sheet and that part will always read cell A1. Only the unlocked references in the formula will move.

Imagine the sales tax rate 0.08 sits in cell E1. Order totals are in column B. To add tax to each order you write in C2:

=B2*$E$1

The B2 is relative so it follows each row, while $E$1 is absolute so it always reads the tax rate.

Fill the formula from C2 down to C2, C3, C4 and watch what happens to each part:

• B2 becomes B3, then B4 (relative, moves)
• $E$1 stays $E$1 every time (absolute, fixed)

So row 3 reads =B3*$E$1 and row 4 reads =B4*$E$1. The tax rate is never lost.

Forget the dollar signs and write =B2*E1. When copied down, E1 slides to E2, E3 which are empty.

Your tax column fills with zeros. The numbers look wrong but the cause is simple: the rate reference was relative when it should have been absolute.

To convert E1 into $E$1 fast, click on E1 inside the formula bar and press F4 once. Excel inserts both dollar signs for you.

This is much quicker and less error-prone than typing the symbols manually, especially in long formulas.

Suppose $B$1 holds a USD-to-EUR rate of 0.92, and dollar amounts run down column A. Convert each one with:

=A2*$B$1

Copy across hundreds of rows and every conversion uses the same rate. Update the rate in B1 once and the whole column recalculates instantly.

Because both parts are locked, an absolute reference holds firm whether you copy down, across, or both.

Copy =B2*$E$1 to the right and it becomes =C2*$E$1: the B moved to C, but $E$1 still did not budge. The lock works in every direction.

Absolute references shine inside functions too. To find each value's share of a fixed total in B1:

=A2/$B$1

Or to compare each score against a target stored in D1:

=IF(A2>=$D$1,"Pass","Fail")

The locked cell stays the reference point across every row.

The real payoff of an absolute reference is a single point of control. Because every formula reads the same locked cell, changing that one cell updates the whole column at once.

Raise the tax rate in E1 from 0.08 to 0.09 and every =B2*$E$1 formula recalculates instantly. No editing dozens of formulas: one input drives them all.

Pros often park their constants, rates, and targets in a small labeled area at the top of the sheet, then reference them absolutely.

This keeps formulas readable and lets you change one input to update the entire report. The dollar sign is what keeps every formula pointed at that input.

Test your grasp of absolute references.

An absolute reference like $A$1 locks both column and row, so it always points to the same cell when copied.

• Use it for shared values like rates, fees, and targets
• Mix it with relative references so only the right parts move
• Press F4 to add both dollar signs instantly

Next you'll lock just one part at a time with mixed references.