Practical technology learning
Excel References: Test Relative, Absolute and Mixed References by Copying
A copied formula is a useful test of whether the spreadsheet expresses the intended rule. Create a short worksheet with invented quantities and a shared factor, then inspect how each reference behaves when the formula moves. The teaching values are not fees, tax advice or business data.
Make the first rule independently checkable
Put quantities 2, 5 and 8 in B2:B4 and a shared factor of 3 in E1. State that column C should show each quantity multiplied by that same factor. Calculate 6, 15 and 24 on paper before entering the formula.
In C2, enter =B2*$E$1. Fill the formula down to C4 and inspect each resulting formula, not just its displayed value. B2 should move with the row; the reference to E1 should remain fixed in this copying exercise.
Compare a deliberately drifting reference
In a separate test column, try the same calculation with E1 written as a relative reference. Copy down and note which cells the formulas now refer to. A blank or unrelated cell can make the resulting number misleading rather than obviously invalid.
Keep this comparison in a disposable practice sheet. Explain the mismatch between “use the shared factor” and “move the factor reference with each row”. Do not repair the displayed numbers manually, because that would conceal the formula error.
Change the shared input once
Change E1 from 3 to 4 and predict the new answers 8, 20 and 32. Confirm that all intended outputs update. Then restore the original fixture so that the next comparison has a known starting point.
This input change checks the dependency rule from another direction. A formula containing a hard-coded 3 might match the first answers but fail this test. Record both the initial and changed-input expectations beside the worksheet.
Use mixed references for a two-direction exercise
Create a separate small multiplication grid with row labels across the top and column labels down the side. Decide which input should keep its row and which should keep its column as a formula is copied across and down.
The reference forms $B2 and B$2 lock different parts. Inspect the copied formula in each corner and calculate a few answers manually. Choose the lock from the movement you intend, rather than adding dollar signs until an output looks plausible.
Review the reference rule
- The intended moving and fixed inputs are documented.
- Copied formulas retain the correct shared input.
- A changed shared factor updates every dependent result.
- Mixed references are tested across and down.
- Expected results are calculated independently.
Carry the habit into larger workbooks
Keep a tiny checked example near the worksheet instructions when teaching the rule to someone else. Later workbooks can add tables or named inputs, but should preserve an equally clear dependency model. Mango Engineers’ Excel course includes reference and formula foundations; discuss the current learning scope with the team.