Free demo · Microsoft Excel · Lesson 5 · Basics · Cell references

Absolute references: lock a cell with the $ sign

The fill handle moves every reference in a formula. When all rows must use one cell, such as a discount rate, lock it with $: press F4.

[h]Course | [h]Fee | [h]Discount | | [b]Rate | [b,fill=#fff2cc]10% Art | 1500 | =B2*$F$1→150 Sport | 1200 | =B3*$F$1→120 Computers | 1800 | =B4*$F$1→180 Music | 1400 | =B5*$F$1→140
  1. 1In C2 type =B2*F1: the fee × the rate in F1
  2. 2Press Enter: 1500 × 10% = 150
  3. 3Fill down: 0, 0, 0? C3 became =B3*F2: F1 slid down to empty cells
  4. 4Edit C2, click on F1 in the formula, press F4: $F$1
  5. 5Fill down again: =B3*$F$1, =B4*$F$1… only the B part moves
  6. ✓Change F1 to 15%: every discount updates at once
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

When you copy a formula with the fill handle, Excel shifts every cell reference with it: that is a relative reference, and it is what makes one formula work for a whole column. It goes wrong when every row has to use the same cell, such as a discount rate, a tax rate or an exchange rate kept in one place. In this lesson the discount formula =B2*F1 is right in the first row, but filled down it becomes =B3*F2 and returns 0, because the rate reference slid down to an empty cell. The fix is an absolute reference: click on F1 inside the formula and press F4, and Excel writes $F$1. The dollar sign before the letter locks the column and the one before the number locks the row, so every row multiplies by the same rate, and changing that one cell updates every result. The full course goes on to mixed references such as $A2 and B$1, which fill a whole multiplication table from one formula, and to percentages and checking formulas with Show Formulas.

Key points

  • Why a formula that is filled down can suddenly return 0
  • $F$1 locks the column and the row: press F4
  • Keep a rate in one cell and change it only once