A copied formula can change without anyone editing its text. That is useful when each row should calculate with its own quantities, but risky when every row must use the same rate. The reliable method is to measure the copy’s movement, then change only the coordinates that are allowed to move.
This lesson uses fictional supply data and ordinary same-sheet A1 references in Microsoft Excel and LibreOffice Calc. It concerns copy and fill operations, not cutting or moving cells. Formulas begin with =; * means multiplication. The examples need no functions or argument separators. Decimal points are shown in English notation; enter numbers using your application’s locale and enter rates such as 8% explicitly as percentages.
Read a reference in two parts
In B4, B identifies the column and 4 identifies the row. A dollar sign fixes the coordinate immediately following it during copying. B4 can change both coordinates; $B$4 fixes both; $B4 fixes only the column; B$4 fixes only the row.
Microsoft’s reference guide and LibreOffice’s address guide describe these reference types. An absolute reference is not a frozen number: changing the value in the referenced cell can still change the calculation.
| Reference in original formula | Reference in copied formula | Reason |
|---|---|---|
| B4 | D7 | Column and row both change |
| $B$4 | $B$4 | Both coordinates are fixed |
| $B4 | $B7 | Column fixed; row moves down three |
| B$4 | D$4 | Column moves right two; row fixed |
A four-step method
- Write the address of the cell containing the original formula and the destination address.
- Find the horizontal and vertical offset. Down and right are positive; up and left are negative.
- Apply those offsets to every unlocked coordinate in each reference.
- Evaluate the copied formula using the destination’s referenced data, then ask whether those are the intended inputs.
The offset comes from the formula’s location, not from the first input it mentions. Copying a formula from F6 to H9 means two columns right and three rows down, regardless of whether its inputs begin at A1 or E5. Treat every reference separately.
Worked case 1: quantities change, one discount stays shared
On a worksheet called Supplies, enter the following data in columns A–C and put 8% in H2. Column D will hold gross cost; column E will hold cost after the shared discount. All prices are invented classroom KSh amounts and do not represent a real offer.
| Worksheet row | A: item | B: quantity | C: unit price, KSh |
|---|---|---|---|
| 4 | Index-card bundles | 7 | 120 |
| 5 | Label rolls | 12 | 35 |
| 6 | Storage sleeves | 5 | 260 |
In D4 enter =B4*C4. Copy downward to D5 and D6. Both references remain relative because each new row needs its own quantity and unit price. In E4 enter =D4*(1-$H$2) and copy down to E6. D4 should change to D5 and D6, while H2 must stay fixed.
| Cell | Formula | Result, KSh |
|---|---|---|
| D4 | =B4*C4 | 840 |
| D5 | =B5*C5 | 420 |
| D6 | =B6*C6 | 1,300 |
| E4 | =D4*(1-$H$2) | 772.80 |
| E5 | =D5*(1-$H$2) | 386.40 |
| E6 | =D6*(1-$H$2) | 1,196.00 |
Check the totals independently: gross cost is 840 + 420 + 1,300 = KSh2,560. Eight per cent of 2,560 is KSh204.80, leaving KSh2,355.20. The three net row values sum to the same result. This check tests both the arithmetic and the decision to share one rate.
If E4 instead used H2 without dollar signs, a downward copy would point to H3, then H4. Those addresses might be empty or contain unrelated numbers. A displayed result that looks plausible is not enough; inspect the formula’s references.
Worked case 2: build a two-direction multiplication grid
Use a separate worksheet, Grid. Enter row factors 3, 5 and 8 in A3:A5. Enter column factors 4, 6 and 9 in B2:D2. In B3, the formula =$A3*B$2 combines the factor on its left with the factor above it.
The A column must stay fixed when copying across, but its row must change when copying down. Row 2 must stay fixed when copying down, but its column must change when copying across. Fill B3:D5 from B3.
| Row factor | Column B: 4 | Column C: 6 | Column D: 9 |
|---|---|---|---|
| A3 = 3 | $A3*B$2 = 12 | $A3*C$2 = 18 | $A3*D$2 = 27 |
| A4 = 5 | $A4*B$2 = 20 | $A4*C$2 = 30 | $A4*D$2 = 45 |
| A5 = 8 | $A5*B$2 = 32 | $A5*C$2 = 48 | $A5*D$2 = 72 |
Each formula shown in the grid is entered with an initial =. Check D5 by its meaning: the row factor is 8 and the column factor is 9, so its value must be 72. Fully absolute references would keep returning the original pair 3 and 4. Fully relative references would drift away from the header row and column.
Worked case 3: trace a copy in both directions at once
On another independent worksheet, the formula in D5 is =B5*$H$2+C$3+$A5. Copy it to F8. D to F is two columns right; row 5 to row 8 is three rows down.
| Original part | Copied part | Explanation |
|---|---|---|
| B5 | D8 | Both coordinates move |
| $H$2 | $H$2 | Neither coordinate moves |
| C$3 | E$3 | Only the column moves |
| $A5 | $A8 | Only the row moves |
The copied formula is =D8*$H$2+E$3+$A8. Suppose B5 = 6, H2 = 8%, C3 = 9 and A5 = 4. The original value is 6 × 0.08 + 9 + 4 = 13.48. If D8 = 15, E3 = 7 and A8 = 5, the copied value is 15 × 0.08 + 7 + 5 = 13.20.
Different output values do not prove a copying mistake. The copied formula deliberately uses different unlocked inputs. Conversely, identical outputs would not prove the references were right: two different cells might happen to hold equal numbers.
How to diagnose a suspicious copy
Start at the first correct formula and compare one copy with the intended row and column. If every row should use a single rate, check both dollar signs around its address. If a grid should use edge labels, check that the row-label column and column-label row are fixed.
Then test a cell away from the starting corner. The bottom-right cell of the multiplication grid exercises both directions; checking only B3 cannot reveal a faulty fill pattern. Avoid making every reference absolute as a blanket repair. That can remove one error while preventing quantities from changing as intended.
Three solved practice tasks
Task 1: copy a relative input and a fixed input
Question: E4 contains =C4+$B$1. Copy it to G6. Given C4 = 14, B1 = 3 and E6 = 22, find both formulas’ values.
Solution: The copy moves two columns right and two rows down. C4 becomes E6; $B$1 stays fixed. G6 contains =E6+$B$1. The original value is 14 + 3 = 17; the copied value is 22 + 3 = 25.
Task 2: check two mixed references
Question: Copy =$B6*C$2 from C6 to E9. Let B6 = 5, C2 = 4, B9 = 8 and E2 = 9.
Solution: The movement is right two and down three. The new formula is =$B9*E$2. Original output = 5 × 4 = 20; copied output = 8 × 9 = 72. The fixed column B and fixed row 2 still identify the intended edges.
Task 3: choose a reusable discount formula
Question: C7 and C8 hold gross amounts 240 and 350. J2 holds 12%. Write E7 so that copying it straight down calculates each net amount using J2.
Solution: Use =C7*(1-$J$2). E8 becomes =C8*(1-$J$2). Results are 240 × 0.88 = 211.20 and 350 × 0.88 = 308.00. Fixing only J$2 would also preserve the rate for this downward-only copy, but $J$2 clearly expresses that the one shared address must remain unchanged in either direction.
Frequently asked questions
- What does the dollar sign lock?
- It locks the coordinate immediately after it when a formula is copied: $B fixes the column, $4 fixes the row, and $B$4 fixes both.
- Is an absolute reference a fixed value?
- No. It keeps referring to the same address during copying. If the value stored at that address changes, formulas using it can recalculate.
- Why use mixed references?
- They let a formula follow one changing coordinate while keeping the other fixed, such as a row label in column A and a column label in row 2.
- Do I count movement from the referenced cell?
- No. First compare the cell holding the original formula with the destination holding its copy. Apply that row-and-column offset to each unlocked coordinate.
- Are copying and cutting the same?
- No. These examples concern copying or filling formulas. Moving cells, cutting, inserting rows and other structural edits can update references differently.
- Does the currency display format make a reference absolute?
- No. Showing a value as money does not add dollar signs to its address. Formula references and number formatting are separate.
- Will the examples work in Excel and Calc?
- They use ordinary same-sheet A1 references and arithmetic supported by both. Enter percentages as percentages; locale-specific decimal entry may differ. No function-argument separator is needed here.
The reference transformations and numerical outputs above were checked with an independent coordinate-offset routine. They are original revision tasks, not official marking schemes or a version-specific menu tutorial.
WhatsApp Us Now