What is G2 Computing | How Spreadsheet References Change When You Copy a Formula

Did you know? Copying the same spreadsheet formula can deliberately change the cells it refers to. That behaviour lets one carefully designed formula calculate an entire list.

This G2 Computing guide explains relative and absolute references by asking a practical question: which parts of the formula should follow the new row, and which should keep pointing to the same cell?

SEAB lists G2 Computing as K237 for the 2027 SEC examination, with spreadsheet skills within its subject content. The examples below teach reference behaviour in standard A1-style formulas. They are original practice examples rather than official assessment tasks.

A relative reference changes when its formula is copied to a new position. If a formula in D2 refers to B2, copying it one row down normally changes that reference to B3.

An absolute reference keeps its row and column fixed during copying. The reference $F$1 continues to point to F1 when the formula is copied down or across.

The dollar signs belong to the reference syntax. They do not turn the cell into a currency amount.

Mixed references fix only one part: $B2 fixes column B while leaving the row relative, and B$2 fixes row 2 while leaving the column relative.

These rules describe copying or filling formulas. Inserting rows, moving cells or changing the spreadsheet structure can involve additional behaviour, so treat those as separate operations.

A formula can calculate the first row correctly even when it is designed badly for the rest of the table.

Suppose every item has a quantity and unit price, and one shared discount rate is stored above the table. The item references should follow each row; the discount reference should continue pointing to the shared rate.

If you make every reference relative, the first result may be correct. After copying, the formula may refer to an empty cell or a different value where the rate should be.

If you make every reference absolute, each row may keep calculating the first item. Again, the first row looks fine while the filled range is wrong.

Therefore, inspect at least one copied formula as well as the first formula. The relationship across the table is part of the solution.

Original practice layout: B2 contains a quantity of 3, C2 contains a unit price of 8, and F1 contains a discount rate entered as 10%. Cell D2 should calculate the amount after discount.

The formula is =B2*C2*(1-$F$1). The quantity and price are relative because each item has its own row. The discount rate is absolute because all rows use F1.

The row-two calculation is 3 × 8 × (1 − 0.10), which equals 21.60.

Now suppose B3 is 2 and C3 is 15. Copy D2 down to D3. The formula becomes =B3*C3*(1-$F$1), giving 2 × 15 × 0.90 = 27.

Only the item row has changed. The shared discount reference still points to F1.

If the original formula had used F1 without dollar signs, copying down would normally change it to F2. The copied formula would no longer point to the stated discount input.

Check the formula and the intended relationship, rather than relying on whether an unintended blank reference happens to produce a plausible result.

Reference	Copied one row down	Copied one column right
B2	B3	C2
$B2	$B3	$B2
B$2	B$2	C$2
$B$2	$B$2	$B$2

A1-style references contain a column and a row. Read B2 as column B, row 2. Then ask whether each part should change during the planned copy.

In $B2, the dollar sign before B fixes the column. Copying one column right keeps B, while copying one row down changes 2 to 3.

In B$2, the row is fixed. Copying one column right changes B to C, while copying one row down keeps row 2.

In $B$2, both parts remain fixed. In B2, both parts are relative.

An efficient memory aid is ‘the dollar sign fixes what immediately follows it’. Then verify the result using an actual copy direction.

Do not equate ‘relative’ with ‘row changes’. Copying down changes relative rows, copying across changes relative columns, and copying diagonally can change both.

Consider a grid with row factors in A2, A3 and A4, and column factors in B1, C1 and D1. Each interior cell should multiply the factor at the left of its row by the factor at the top of its column.

In B2, use =$A2*B$1. The first reference must stay in column A while following the row. The second must stay in row 1 while following the column.

Copying B2 one cell right to C2 gives =$A2*C$1. Copying B2 one cell down to B3 gives =$A3*B$1.

If A2 is 2 and C1 is 5, C2 gives 10. If A3 is 3 and B1 is 4, B3 gives 12.

Mixed references are helpful here because the grid has two directions of variation. A shared single input such as a discount rate is a different layout and often needs a fully absolute reference.

The useful question is not ‘Which reference type is best?’ It is ‘Which coordinates should remain tied to this part of the model?’

In the discount example, entering 10% represents a value of 0.10. The expression 1 − 0.10 gives 0.90, the fraction of the original amount remaining after the discount.

Entering 10 as an ordinary number is different. A formula that subtracts 10 from 1 does not describe a ten-percent discount.

Formatting a number as a percentage changes how it is displayed; it does not necessarily turn an already entered 10 into the intended decimal value. Check both the underlying value and the display.

Likewise, currency formatting can display 21.6 as 21.60 without changing the reference logic. A tidy display is not evidence that the formula points to the right cells.

For this practice task, calculate a small example by hand and compare it with the spreadsheet. The expected amount after a 10% discount should be 90% of the original positive amount.

This reasonableness check complements formula inspection. It may expose a wrong rate even when the references themselves are copied correctly.

Task 1: D2 contains =B2*C2. Copy it to D3. Answer: =B3*C3.

Task 2: D2 contains =B2*$F$1. Copy it to E2. Answer: =C2*$F$1. The relative column changes, while the fixed reference remains F1.

Task 3: B2 contains =$A2*B$1. Copy it to C3. Answer: =$A3*C$1. The row of the first reference and the column of the second reference change.

Task 4: every row must use the rate in H2. Which reference fixes both parts? Answer: $H$2.

Task 5: one row uses quantity 4, price 12 and a shared discount of 25%. Calculate the after-discount amount. Answer: 4 × 12 × (1 − 0.25) = 36.

Task 6: the first result is correct but every copied row uses the first item’s quantity. What should you inspect? Answer: whether the quantity reference was fixed when it should have followed the row.

Extension: design a two-row table where copying a wrongly relative rate reference visibly changes the answer. Put a clearly different value in the unintended destination cell so the reference error is easy to see.

Predict first, copy second and inspect third. That sequence gives you evidence about the rule, rather than a habit of filling and hoping.

Before entering a formula, describe it in words: ‘this row’s quantity times this row’s price times the remaining proportion’. Then identify which input is shared.

Check the first row manually. Copy the formula to one new row and inspect the references. Only then fill the larger range.

Change the shared rate as a test. All affected results should recalculate using that input. Also change one item’s quantity; unrelated item rows should not unexpectedly change.

Frequently asked question: does an absolute reference stop the input from changing? No. It fixes the address during copying. If the value in the referenced cell changes, formulas using it can recalculate.

Frequently asked question: should I type the rate directly into every formula? A shared input cell can make a model easier to update and inspect. Follow the task’s stated requirements.

Frequently asked question: do all formulas need dollar signs? No. Use them only for the parts that should stay fixed under the intended copy.

Next step: take one school spreadsheet, identify one row-specific input and one shared input, and explain the reference choices in a copied formula.

Sources and continue reading

Curriculum references checked on 25 October 2026. All scenarios, calculations and practice answers in this article are original teaching examples.

• SEAB: G2 Computing K237, 2027
• SEAB: 2027 SEC G2 syllabuses for school candidates
• Read the previous article for this subject
• eduKate SEC subject study guide index