Modeling discipline
Relative, absolute and mixed references
Write one formula that copies correctly across a whole row, column or grid.
Learning objectives
- Distinguish relative, absolute and mixed references
- Predict how a formula changes when it is copied
- Build a forecast row and a two-way grid from a single copied formula
Professional models are built by writing a formula once and copying it across years and down line items. That is faster, and it is safer: when every cell in a row holds the same formula, one check of the first cell checks them all, and an inconsistent cell stands out.
This only works if each reference behaves correctly when copied. Some references should move with the formula, like last year's revenue. Others must stay put, like a single growth assumption.
B3 → moves both ways · $B$3 → never moves · B$3 → row fixed · $B3 → column fixed
- $ before the letter
- locks the column
- $ before the number
- locks the row
- F4
- cycles a reference through B3 → $B$3 → B$3 → $B3 while editing
Included with a Formula Quest membership
The rest of this lesson is for members. Members get the full lesson, including its 3 graded exercises, and every other Finance Quest lesson.