Gnumeric · Names and references · Beginner
Copy Formulas with Fixed and Mixed References in Gnumeric
Audit copied formulas in a Gnumeric price matrix using relative references, fixed rows, fixed columns and one absolute rate cell.
Documentation and source-backed procedure; independent fixture checks; native verification pending · Editorial standards
Make a price matrix in which each formula uses a quantity from the left, a unit price from above and one common surcharge. Copying should change only the intended coordinates.
Prepare the inputs
Use a blank worksheet with ordinary A1-style references. Enter Quantity in A2; put quantities 2, 4, 6 in A3:A5. Enter unit prices 5, 7, 10 in B2:D2. Put Surcharge in G1 and 0.10 in G2. Leave B3:D5 empty.
The intended calculation is quantity Ă— unit price Ă— (1 + surcharge). This synthetic matrix compares three prices at three quantities; it is not a tax calculation.
Copy one checked formula
- In B3 enter
=$A3*B$2*(1+$G$2). Expect 11. - Select B3 and choose Edit → Copy. Select C3:D3 and choose Edit → Paste to repeat that one-cell formula across the two destinations.
- Select B3:D3 and copy it. Select B4:D5 and paste it into the two rows below. If your installed version requires a matching-sized selection, paste the copied row at B4 and again at B5.
- Inspect the editing area for each result, not just its number. Use Copy rather than Cut: moving an existing formula has different reference-maintenance behavior.
The cell reference documentation defines a dollar sign before the coordinate that must stay fixed. Here $A3 keeps column A but lets the row change; B$2 keeps row 2 but lets the column change; $G$2 fixes both.
Check all nine addresses
| Cell | Expected formula | Expected value |
|---|---|---|
| B3 | =$A3*B$2*(1+$G$2) |
11 |
| C3 | =$A3*C$2*(1+$G$2) |
15.4 |
| D3 | =$A3*D$2*(1+$G$2) |
22 |
| B4 | =$A4*B$2*(1+$G$2) |
22 |
| C4 | =$A4*C$2*(1+$G$2) |
30.8 |
| D4 | =$A4*D$2*(1+$G$2) |
44 |
| B5 | =$A5*B$2*(1+$G$2) |
33 |
| C5 | =$A5*C$2*(1+$G$2) |
46.2 |
| D5 | =$A5*D$2*(1+$G$2) |
66 |
To include a fully relative reference, enter =SUM(B3:D3) in E3 and copy it to E4:E5. The copied ranges should be B4:D4 and B5:D5. Expected row totals: 48.4, 96.8, 145.2. Their grand total is 290.4.
These numbers also have an independent structure check: doubling the quantity from 2 to 4 doubles every result; tripling it to 6 triples every result. A correct first cell does not establish that its copies work.
Change one kind of input at a time
Change C2 from 7 to 8. Only the middle price column should change, becoming 17.6, 35.2, 52.8. The other six matrix cells remain unchanged. Restore C2 to 7.
Change G2 from 0.10 to 0.20. All nine results should change. The first row becomes 12, 16.8, 24; the second 24, 33.6, 48; the third 36, 50.4, 72. The grand total becomes 316.8. Restore G2 to 0.10 and confirm 290.4.
Advanced case: diagnose a plausible copying error
In a disposable copy, replace B3 with =A3*B2*(1+G2), removing every dollar sign. B3 still shows 11, so checking only the starting result misses the fault. Copy it once to C3. The expected translated expression is =B3*C2*(1+H2): with H2 blank in this fixture, the result is 77, not 15.4.
Inspect the addresses to explain the failure. Quantity drifted to a result cell, and the surcharge drifted out of G2. Restore the correct formula in B3, copy it across and down again, then compare all nine expressions with the table. Restore the E-column totals if the experiment overwrote them.
Recovery and limits
Save a native workbook after the matrix passes. Reopen it and repeat the C2 test; this checks both stored formulas and their dependencies. If a paste overwrites an input, use Undo promptly or reopen the saved checkpoint instead of guessing the missing value.
Absolute references do not mean “immutable input.” Editing G2 still changes every dependent result. They control reference translation during copying. Inserting/deleting rows, moving cells and copying to another workbook introduce other rules and are outside this exercise.
Evidence and related work
Reviewed 2026-10-19 against cell referencing in the Gnumeric 1.12 manual and Edit-menu copy/paste commands. Formula translations and expected values were checked independently; no native paste, reopen or screenshot is claimed. Installed-version selection behavior remains to be verified with the explicit nine-cell checklist.
See SUM for row controls and file-format compatibility before exchanging the workbook.