Absolute Reference

Also known as · mixed reference

A reference with a $ in front of the column letter, the row number or both, which freezes that part of the address when the formula is copied: $B$1 stays fixed, $A2 locks the column only, B$1 locks the row only. Pressing F4 on a reference cycles through the styles. Also in Lec 02, Lec 03, Lec 06.

When to use

Used whenever one formula is filled down or across and must keep pointing at an input cell (a threshold, a bonus amount, a lookup table), as in the multiplication table =B$1*$A2 and the bonus rule =IF(J2>$K$204,$L$204,$L$205). Forgetting the $ makes the reference slide and the results turn to nonsense.

See Lec 01.

Appears in

No references yet.