Cell references in spreadsheets are relative by default. This means that they show a relationship to other cells. When a formula containing relative references (e.g. A2, B2) is copied to a different cell, it maintains its relative references.
The formula recognises where the cells it refers to are, in relation to its own cell, and changes the cell references in the formula to keep the same positional relationship.
For example, formula =A2*B2 is placed in cell C2. (The cost multiplied by the number bought gives the amount of money received.)
The formula is then copied from cell C2 to cell C3.
The following process happens:
- The formula recognises that it has been placed in a different cell (C3).
- It looks at the relative positions of all the cells involved in the original formula in cell C2 (i.e. cell A2 was two cells to the left of C2; cell B2 was one cell to the left of C2.)
- To maintain the relative positions of the cells, it changes its formula to reference the cell two to its left and the cell immediately on its left.
- Because it has been moved from C2 to C3, these cells change to A3 and B3 and the formula becomes =A3*B3.
Finally, the fomula from cell C2 is copied to cell C4. The formula changes to A4*B4, once again referencing the same two relative cells, as in the original formula.
See: absolute cell reference.