This tool is a small spreadsheet grid where you can copy one formula to other cells and see, reference by reference, what moved and what stayed put. A reference without $ is relative: it shifts when the formula is copied. A reference with $ is absolute: the part after the $ is locked.
It supports the skills in spreadsheet formulas and spreadsheet modelling and charts for IGCSE ICT.
How do I use the grid?
- Edit the grid. It has columns A to E and rows 1 to 6. Type a number, or a formula that starts with
=, into any cell. - Choose “Copy formula from” and pick the cell that holds the formula you want to copy.
- Choose “Paste starting at” and pick the first target cell.
- Choose how many cells to fill down (1 to 5), then press Copy and paste.
- Read the copy trace. Each row shows the target cell, the formula pasted, what changed and the recalculated value.
- Press Reset grid to return to the starting numbers.
How do I read the result?
The “What changed” column explains every reference in plain words. It tells you when a reference stayed fixed because of $, when only the row or only the column moved, and when nothing changed because the copy offset was 0.
If a cell shows an error code, a box called “Error explanations” appears below the grid and says what that code means. Supported functions are SUM, AVERAGE, MIN, MAX and COUNT, plus + - * / ^ and brackets.
Worked example
The starting grid holds A1 = 10 and B2 to B5 = 3, 4, 5, 6. Cell C2 holds =$A$1*B2, which gives 10 × 3 = 30.
Copy C2 and paste it starting at C3, filling down 3 cells.
| Target | Formula pasted | What changed | Value |
|---|---|---|---|
| C3 | =$A$1*B3 | $A$1 stays fixed, B2 becomes B3 (row moved 1 down) | 40 |
| C4 | =$A$1*B4 | $A$1 stays fixed, B2 becomes B4 (row moved 2 down) | 50 |
| C5 | =$A$1*B5 | $A$1 stays fixed, B2 becomes B5 (row moved 3 down) | 60 |
Check one by hand: C4 = 10 × 5 = 50. The multiplier in A1 stayed the same because of the two $ signs, while the price column moved down with the formula.
What goes wrong, and how do I test it?
Now change C2 to =A1*B2 (no $) and copy it down one cell. The pasted formula is =A2*B3. A2 is empty, so the answer is 0, and the tool shows that A1 became A2.
This is the classic mistake: a fixed input such as a rate or multiplier was never locked. Fix it by adding $ to the cell that must not move.
Try a mixed reference too. Copy =$A1*B1 across and down and watch that the column letter of A stays locked while the row number still moves.
Assumptions and limits
The grid is tiny on purpose, so the rules are easy to see. It has no macros, no file loading and no fancy functions. It does not claim that every detail matches Excel or Google Sheets, so check any formula you plan to hand in by testing it in your own software.
Which lessons explain this?
Start with building a formula with correct relative references, then using an absolute reference deliberately. If your worksheet keeps shifting when you copy it, read my spreadsheet references move incorrectly when copied. Finish with the spreadsheet formulas practice set.
If you would like a teacher to go through your own spreadsheet tasks one to one, see IGCSE ICT tuition. More practice aids are on the tools page.