2.1 Relative, absolute and mixed references

What you'll be able to do

Write one formula and fill it across a whole grid. Know before you look that every copy landed right, because you chose which parts of it move and which stay.

Before you start

Open the formula drills workbook. The reference drills in it are built for this lesson.

The work

1. Same either way: one cell address can be written four ways, and each behaves differently when the formula is copied. A1 moves in both directions. $A$1 never moves. $A1 keeps its column and lets its row move. A$1 does the opposite. The dollar sign holds whatever it stands in front of. That is the whole rule, and the rest of this lesson is practice.
2. Same either way: never type the dollar signs. Edit the formula with F2, put the insertion point on the reference, and press F4. It cycles A1 → $A$1 → A$1 → $A1 and back around. Stop at the one you mean.
3. Same either way: to fill a formula down, select it with the empty cells below and press Ctrl+D. Use Ctrl+Shift+Down Arrow to select to the end of the block. Then check one copy: open it with F2. Excel colours each reference and the cell it points at, so you can see where the copy landed.
4. Same either way: the best practice for mixed references is a multiplication grid. Put numbers down column A and numbers across row 1. In B2 write one formula, =$A2*B$1. The column is held on the first reference, the row on the second. Fill it right and down, and every cell is correct. Work out why before you move on: each copy always needs column A, and always needs row 1.
5. Same either way: when a value has a meaning, a name beats dollar signs. Select the tax-rate cell and give it a name: press Alt+M, M, D to open the Define Name dialog on the Formulas tab, and call it TaxRate. Now write =B2*TaxRate. It reads clearly and copying cannot move it. Press F3 inside any formula to paste from the names you have defined.
6. Same either way: a reference can cross sheets. =Sheet2!B4 reads from another sheet, and typing the sheet name with an exclamation mark works in any formula. References to other workbooks work too, but they break when files move, so bring the data into one workbook where you can.

Try it

Do the reference drills in the workbook, then build the multiplication grid from one formula. Before each fill, say out loud what will move. After it, open a far corner with F2 and check that you were right.

Check yourself