Lesson 3 of 45, Foundations
Relative vs absolute references
Quick answer
The dollar sign decides what moves when you fill a formula.
- Key formula
=B2*$F$2(B2 moves, F2 stays)
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
When you fill a formula down or across, Excel shifts its references by the same amount. That is why =B2*C2 becomes =B3*C3 one row down. These are relative references.
A dollar sign locks part of a reference. $F$2 never moves. $A2 keeps column A but lets the row change; B$1 keeps row 1 but lets the column change. In desktop Excel, press F4 (⌘+T on Mac) after a reference to cycle through the options.
Lock any single input every row shares: a tax rate, a total, an exchange rate.
Syntax
=B2*$F$2B2 moves, F2 stays=B2/$B$7Every row divides by the total=$A2*B$1Mixed: column A and row 1 lockedWatch out for
- Filled rows that show 0 or #DIV/0! usually mean a reference that should be locked drifted onto an empty cell.
- The grader requires one formula filled across the range, so typing a separate formula in each row won't pass.
The exercises
- Sales tax. In C2, calculate the tax on each price using the rate in F2. Fill down to C6.
- Share of total. In C2, divide each region's sales by the total in B7. Fill down to C6.
- Mixed references. Fill B2:F6 with one formula that multiplies the number in column A by the number in row 1. Write it in B2, then fill right and down.
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Sales tax
C2, calculate the tax on each price using the rate in F2. Fill down to C6.