First principles. A cell reference like B2 inside a formula works less like the name of a cell and more like a set of directions, measured from the cell doing the pointing. When cell C2 contains =B2*0.045, Excel doesn't store "multiply the contents of B2." It stores something closer to "multiply the cell one column to my left." So the formula still works when you copy it down. Paste it into C3 and "one column to my left" now points at B3. Paste it into C10 and it points at B10. This is the relative reference. It's the default, and it's what makes a spreadsheet more than a very expensive calculator. You write the logic once (rep's payout equals the sales figure beside it) and the relative reference carries that logic down eight rows or eight thousand.
Why does a copied formula need dollar signs?
The problem arrives the instant a formula needs to point at something that must not move as it travels. Suppose the commission rate is stored in one cell, F1, and every rep's payout is their sales × that one rate. You write =B2*F1 in C2. It gives the right answer. You fill it down. Row 3 becomes =B3*F2 and row 4 becomes =B4*F3. F2 and F3 are empty cells, so every payout below the first collapses to zero. Your relative reference did what it always does and moved both pointers down together. You wanted the sales pointer to move and the rate pointer to stay.
Advertisement
That's what the dollar sign does.
A $ before a coordinate freezes that coordinate when the formula is copied. There are three ways to freeze, giving three kinds of reference beyond the plain relative one:
| Written as | Name | Column when copied | Row when copied | Use it when… |
|---|---|---|---|---|
F1 | Relative | Moves | Moves | the pointer should track alongside the formula (the sales beside each rep) |
$F$1 | Absolute | Frozen | Frozen | one fixed cell feeds every formula in the fill (the single rate, a tax %, a spot FX) |
F$1 | Mixed — row locked | Moves | Frozen | filling down past a header row that must stay row 1 |
$F1 | Mixed — column locked | Frozen | Moves | filling right past a label column that must stay column F |
Read the dollar sign literally. It means "the coordinate immediately after me does not change." $F$1 freezes both, so it always points at F1 no matter where you paste it. That's the absolute reference, and it fixes the commission grid. Write =B2*$F$1 and fill down, and every row now reads =B3*$F$1, =B4*$F$1 and so on. Now the sales pointer walks down while the rate pointer stays nailed to F1.
How do mixed references work?
Mixed references carry one dollar sign only. Beginners skip them and professionals can't live without them, because they let a single formula fill a two-dimensional grid. The canonical case is a sensitivity table. Loan principals run down column A, interest rates run across row 1, and the body of the grid shows annual interest = principal × rate. The body formula, written once in the top-left body cell B2, is =$A2*B$1.
Take the next few sentences slowly. Most people skim them once and pay for it later. Walk through why each half is half-frozen. In $A2 the column is frozen to A, because every body cell must read its principal from column A however far right it is. The row is free, because each row reads its own principal. In B$1 the row is frozen to 1, because every body cell must read its rate from row 1 however far down it is. The column is free, because each column reads its own rate. Fill that one formula across and down the whole grid and every cell is correct. C5 becomes =$A5*C$1, pointing at the principal in A5 and the rate in C1. One formula, filled two ways, no errors. Get the mixed references wrong and you'll either drag a corner value across the whole table or generate a diagonal of nonsense. Get them right and you've built the engine of every DCF sensitivity you'll ever run.
How do you decide which reference to use?
Run one mental test before you fill, every time. Ask of every reference in the formula: "when this formula lands in its farthest destination cell, does this pointer need to have moved, or stayed?" If it moved, leave it relative. If it stayed, freeze the coordinate that must not change. Answer that question deliberately and you'll never fill a broken column again.