Spreadsheet Practice

Lesson 3 of 29, Foundations

Relative vs absolute references

The dollar sign decides what moves when you fill a formula.

Free9 minutes, 3 exercises$A$1$A1A$1

Watch it done

4 steps

Watch 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 locked

Watch 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

  1. Sales tax. In C2, calculate the tax on each price using the rate in F2. Fill down to C6.
  2. Share of total. In C2, divide each region's sales by the total in B7. Fill down to C6.
  3. 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.

Sales tax

In C2, calculate the tax on each price using the rate in F2. Fill down to C6.