Cell Reference

A cell reference is how a formula names a cell instead of containing a value: B2 is column B, row 2, and B2:E2 is a range of four cells. References are what make a spreadsheet recalculate — change the referenced cell and everything pointing at it updates — and what make one formula reusable across hundreds of rows.

Their behavior on copy is the part to learn deliberately. Microsoft states that “when you copy a formula that contains a relative cell reference, that reference in the formula will change”, shifting by however far the formula moved, while an absolute reference written with dollar signs “stays exactly the same”. A mixed reference locks one half: $B4 keeps the column while the row moves, B$4 the reverse. In Excel, F4 cycles a selected reference through the four forms.

Relative behavior is the default because it is usually what you want: a row total copied down each row should follow its own row. The bug appears the moment one part of the formula should not move — a tax rate, a conversion factor, a lookup table. Filled downward without dollar signs, that reference walks off the value and into the empty cells below it, producing a correct first row and quietly wrong ones after it. For example, with a tax rate in E1, the first row =B2*E1 is right, but copying it down gives =B3*E2, which multiplies by an empty cell. A column that looks right at the top and wrong lower down is therefore often a missing $ (the anchored form is =B2*$E$1), although data types, ranges, or duplicate keys can produce a similar pattern, so check the copied formulas before assuming.

Two further habits keep references durable. Prefer a range function such as =SUM(B2:B4) over =B2+B3+B4, because a row inserted inside the range is included automatically, and deleting one row inside the range shrinks the range instead of turning the formula into #REF!, which the single-cell reference B3 in =B2+B3+B4 would. The range does not cover a row inserted just below its last cell, and deleting every cell it covers still gives #REF!; Excel's error checking flags formulas that may omit cells in a region.

Consider naming an important constant rather than pinning it with dollar signs, since a name explains what the cell holds and does not have to be re-checked every time the formula is copied.

References

• Switch between relative, absolute and mixed references
• Detect errors in formulas