Absolute references in Excel: the $ sign and F4

Write a formula once, copy it down a column, and every row works itself out. That only works because references move when they are copied, and the one that mustn't move gets a $ sign. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

Relative references move

A reference such as B2 is relative: Excel stores it as "the cell this far away from me". Copy a formula one row down and every relative reference in it moves one row down too. That is why copying works at all.

In this example, set in the Concert tour, revenue is tickets times price. In D2, =B2*C2 gives 12,274. Filled down, D3 gets =B3*C3, D4 gets =B4*C4, and so on, without typing any of them.

To fill down, select the formula and the cells under it and press Ctrl + D (Cmd + D works too in Excel for Mac). Or drag the small square on the corner of the cell, or double-click it to fill as far as the data next to it goes.

Absolute references stay put

Each row's share is its revenue divided by the total, 155,300, in D9. The first row's divisor is the total cell, and so is every other row's. If that reference moved when copied, the second row would divide by the empty cell under the total.

A $ sign fixes the part that follows it. $D$9 means column D and row 9, wherever the formula is copied. So =D2/$D$9 gives 7.9%, and copied down, only the D2 part moves.

Without the dollar signs, the second row's copy divides by the cell below the total, which is empty: =D3/D10 gives #DIV/0!. The lesson checks this before you copy: an unlocked formula is right for the first row, so it is refused with a note to lock it.

F4 adds the dollar signs

Type the reference, then press F4 straight away. Each press moves on to the next of four kinds:

$D$9

Both locked. Copy it anywhere and it still points at D9. This is the one for a total, a rate or any single setting cell.

D$9

Row locked. Copy it across and the column moves, copy it down and the row stays.

$D9

Column locked. The row moves, the column doesn't. Useful in tables that are filled both across and down.

D9

Back to relative.

On most Mac keyboards F4 needs the fn key: fn + F4. Excel for Mac also has Cmd + T for the same thing.

A share is a percentage

7.9% is the cell's value shown as a percentage. The cell itself holds a fraction of 1, and the column is formatted to show it with a % sign. Multiplying by 100 in the formula as well would show it a hundred times too big. The percentage lesson goes further.

The lesson, step by step

1. 1 Work out one row

   In D2: =B2*C2 gives 12,274.

2. 2 Copy it down

   Fill D2 down over D2:D8. The last one, D8, becomes =B8*C8.

   They give 16,830 36,036 26,230 13,362 39,150 11,418

3. 3 Total the revenue

   In D9: =SUM(D2:D8) gives 155,300.

   Named mistake: =SUM(D2:D7) gives 143,882. That stops one row early, at D7. The range goes down to D8.

4. 4 Lock the total

   In E2: =D2/$D$9 gives 7.9%.

   Named mistake: =D2/$D$9*100 gives 790.3%. Leave out *100. The cell is set to show a percentage, so Excel does that part.

   Checked too: Right here, but it will break when copied, because D9 will move. Lock it as $D$9.

5. 5 Copy the share down

   Fill E2 down over E2:E8. The last one, E8, becomes =D8/$D$9.

   They give 10.8% 23.2% 16.9% 8.6% 25.2% 7.4%

In real Excel

All of this works the same on Windows and Mac, and in Excel for the web. In a formatted table (Insert, Table) Excel writes column names instead of cell addresses and fills the column for you, which avoids most locking problems. Naming the total cell does too: a name always points at the same cell, so it never needs dollar signs.

Keep going

1. 3
   Next up is Percentages: percentage formulaWork out percentage change, then plan next year with a growth rate.
2. 3
   Percentages: percentage formulaWork out percentage change, then plan next year with a growth rate.
3. 1
   Totals: SUM formulaAdd up a column, then find the average, the highest, the lowest and the count.