Relative and absolute references

Most Excel screening questions come down to one idea: what happens to a formula when you copy it. If you understand relative and absolute references, you can answer half the test without memorising functions.

Relative references move

=A2*B2 in cell C2, copied down to C3, becomes =A3*B3. Excel does not store "A2"; it stores "two columns to the left, same row". That is what makes filling a column down one drag instead of a hundred edits.

Absolute references stay

Suppose the tax rate sits in F1 and you write =B2*F1 in C2. Copy it down and C3 reads =B3*F2, which is empty. Lock the rate with dollar signs: =B2*$F$1. Now every copy points at F1.

Mixed references

$F1 locks the column but lets the row move; F$1 locks the row but lets the column move. Mixed references are what make a multiplication table from one formula: =$A2*B$1 copied across and down.

A quick test for yourself

Put 10 in A1 and 2 in B1. In C1 write =A1*B1 and copy it to C2. C2 shows 0, because A2 and B2 are empty. Now write =$A$1*$B$1 in C1 and copy it: C2 shows 20. Press F4 while editing a reference to cycle through the four forms.

The Excel test has two questions on this, and interviewers ask it in words: "What would this formula become if I copied it one row down?" Say which parts move and which stay.