When a formula is filled (dragged with the fill handle, or copied and pasted), the spreadsheet doesn't copy it exactly: it adjusts each cell reference by how far the formula has moved. Write a formula, then drag the small square at the corner of its cell down or across (or press Fill). Every copy is listed, with the parts that moved in amber and the parts the $ signs kept fixed in blue.
- A relative reference (B2) moves with the formula: filled down one row it becomes B3; filled across one column, C2.
- An absolute reference ($G$1) never moves: use it for one fixed cell that every copy needs, such as a tax rate, a total or a lookup table.
- A mixed reference fixes just one part: $A2 keeps the column but lets the row move; B$1 keeps the row but lets the column move. A times table or a discount grid needs both kinds.
- A named cell (such as Rate) always means the same cell, so it works like an absolute reference.
Click a column letter or row number in The formula's references to put a $ in front of it or take it away (in a spreadsheet, F4 cycles through A1, $A$1, A$1 and $A1). Formulas, in the title bar, shows the formulae in the cells instead of their values. In Challenges, each formula breaks when it's filled: fix it so every copy is right.
Common exam mistakes: leaving a rate or total relative, so the copies point at empty cells (giving 0 or #DIV/0!); making everything absolute, so every copy gives the same answer; putting the $ on the wrong part of a mixed reference; forgetting that a lookup table's range must be absolute; saying a fill down changes the column letters (it changes only the row numbers).
Objective: Cambridge IGCSE ICT (0417) section 20, spreadsheets: relative and absolute cell references, replication of formulae, and named cells and ranges; Cambridge International AS Level IT (9626) spreadsheets: absolute, relative and mixed references.
Where this fits
- Cambridge: Cambridge IGCSE Information and Communication Technology (0417); Cambridge A Level Information Technology (9626); Cambridge AS Level Information Technology (9626) Goes beyond Cambridge IGCSE Information and Communication Technology (0417): Mixed references ($A2, B$1) go beyond 0417, which asks for absolute and relative references only.
- Pearson Edexcel International: Edexcel International GCSE ICT (4IT1) Goes beyond Edexcel International GCSE ICT (4IT1): Mixed references ($A2, B$1) go beyond 4IT1, which names absolute and relative referencing only.
