Relative and absolute references practice exercises

Fill one formula down and across without breaking it: lock a cell with $, lock only a row or a column, and build a range that grows as you fill.

Start the first exercise

How absolute and relative references work

By default a reference is relative. Fill =B2*C2 from row 2 down to row 3 and Excel turns it into =B3*C3. A dollar sign locks the part right after it: $F$1 locks both the column and the row, F$1 locks only the row, and $F1 locks only the column. F4 cycles through all four versions while the cursor is in a reference.

In =B2*$F$1, the price in B2 moves as you fill down while the tax rate in F1 stays put. With a price of 200 and a rate of 0.25, it returns 50. A price grid needs both directions: =$B2*E$1 keeps the price column and the units row fixed, so one formula fills the whole table. For a running total, =SUM($B$2:B2) locks the start of the range and lets the end grow.

The usual mistake is forgetting the lock. The formula slides down into empty cells, and you get 0 or numbers that make no sense. If a filled formula looks wrong, click a wrong cell and see which reference moved. The exercises below cover locking a rate cell, a price grid with mixed references and a running total.

  1. 1Lock a rate cell with $In C2, work out the tax on the price in B2 with the rate in F1. Lock F1 with $ so the formula can be filled down: =B2*$F$1.
  2. 2A price grid with mixed referencesPremiumIn E2, multiply the price in B2 by the units in E1 so the formula can be filled across to G2 and down to row 4: lock the column of the price and the row of the units, =$B2*E$1.
  3. 3A running total with a growing rangePremiumIn C2, start a running total that can be filled down to C7: SUM a range that starts at a locked $B$2 and ends at a relative B2.

Functions in this topic

Read the guide

Questions

How do I add a dollar sign to a cell reference quickly?

Put the cursor in the reference, either in the cell or in the formula bar, and press F4. The first press gives $B$2, the second B$2, the third $B2 and the fourth goes back to B2. On many laptops you need to hold Fn as well.

Why does my formula give 0 or wrong numbers when I fill it down?

A reference that should stay put is probably not locked, so it moves down with the formula and ends up on an empty cell. Click one of the wrong cells and look at which reference has shifted. Add $ in front of the column letter and row number of that reference, like $F$1, and fill again.

Can I use a name instead of dollar signs?

Yes. A named cell or range always acts as an absolute reference. Select the cell, type a name like TaxRate in the Name Box and press Enter. Then =B2*TaxRate works when filled down. A name also makes the formula easier to read, but you can't see which cell it points to without checking.