onlyoffice · Formula workflow · Intermediate
Copy Formulas with the Right References in ONLYOFFICE
Copy relative, absolute and mixed references across a price matrix and verify both formulas and calculated results.
Quick answer
Use $B4 to lock a column, C$3 to lock a row, and $B$1 to lock both. Copy a small formula across and down, then inspect representative formula text as well as its numeric results.
Version and evidence
ONLYOFFICE Desktop Editors 10.0.0 documentation baseline, English desktop interface on Windows/Linux; official help and desktop changelog checked 2026-10-19. No native application execution or screenshots were obtained. Exact installed build, OS behavior and saved-file round trips remain manual verification requirements; hosted and mobile behavior is outside scope. The steps below are a documented procedure with independently calculated checkpoints, not a native test report. Use an editable .xlsx practice copy. Do not treat the online help as proof of every older desktop build.
A copied formula is a small model of which inputs may move and which must stay fixed. Build a price matrix where each row is a product, each column is a discount scenario, and one tax rate applies everywhere. Check both the numeric answer and the formula text: a plausible answer can hide the wrong reference.
Build the input grid
Create reference-practice.xlsx and rename the sheet Prices. Enter the following inputs:
| Cell/range | Input |
|---|---|
| A1 | Tax rate |
| B1 | 20% |
| A3 | Product |
| B3 | Base price |
| C3:E3 | 0%, 10%, 25% |
| A4:A6 | Pen set, Notebook, Desk kit |
| B4:B6 | 10, 25, 80 |
C3:E3 are numeric percentages, not labels containing text. Format C4:E6 as numbers with two decimal places if desired. Leave G:J empty for a separate relative-reference exercise.
Prove the simple relative case
In G3 and H3 enter Qty and UnitPrice, and I3 enter Line value. Type quantities 2, 3, 4 in G4:G6 and prices 10, 25, 80 in H4:H6. In I4 enter =G4*H4. Copy I4 into I5:I6 using ordinary cell copy or the fill handle. Inspect the formula bar: I5 should contain =G5*H5, I6 =G6*H6. Results are 20, 75, 320; their total is 415.
Both the row and column parts of G4 and H4 are relative. Copying down moves their row numbers; copying across would move the column letters too. Do not use cut-and-move as the experiment: relocating a formula is a different operation from copying it.
Design the two-dimensional formula
On Prices, select C4 and enter =$B4*(1-C$3)*(1+$B$1). The terms have separate jobs:
$B4always reads the base-price column B, but its row can become 5 or 6.C$3always reads the discount header in row 3, but its column can become D or E.$B$1always reads the single tax rate, regardless of copy direction.
Copy C4 across to D4:E4. Inspect D4: =$B4*(1-D$3)*(1+$B$1). Then copy the completed range C4:E4 down through row 6. Inspect C5: =$B5*(1-C$3)*(1+$B$1), and E6: =$B6*(1-E$3)*(1+$B$1).
The dollar sign locks the part immediately following it. Typing it explicitly is the least keyboard-dependent method. ONLYOFFICE also documents F4 for switching reference type while editing a formula, with an Fn variant on macOS. Keyboard interception differs by OS; this Windows/Linux-oriented exercise does not depend on the shortcut.
Reconcile all nine results
| Product | 0% discount | 10% discount | 25% discount |
|---|---|---|---|
| Pen set | 12.00 | 10.80 | 9.00 |
| Notebook | 30.00 | 27.00 | 22.50 |
| Desk kit | 96.00 | 86.40 | 72.00 |
For E6, the independent calculation is 80*(1-0.25)*(1+0.20)=72. For D5 it is 25*0.9*1.2=27. Column totals are 138, 124.2 and 103.5. These arithmetic checks were calculated independently; they do not establish that the application copied or saved the formulas correctly.
Now change B1 to 10%. The matrix should become:
| Product | 0% discount | 10% discount | 25% discount |
|---|---|---|---|
| Pen set | 11.00 | 9.90 | 8.25 |
| Notebook | 27.50 | 24.75 | 20.625 |
| Desk kit | 88.00 | 79.20 | 66.00 |
E5 may display 20.63 with two-decimal formatting while retaining the underlying 20.625. Do not mistake display rounding for a ROUND calculation. Restore B1 to 20% before saving the main workbook.
Save, reopen and test a separate scenario
Save and close reference-practice.xlsx, reopen it and activate Prices. Verify B1 is 20%, E6 is 72, and the three representative formulas above remain formulas rather than pasted values. Confirm the independent relative-reference example still totals 415.
Use Save As to create reference-new-discount-test.xlsx. Enter 50% in F3. Copy E4:E6 one column right into F4:F6. The formulas should refer to F$3 while keeping $B4:$B6 and $B$1. Results should be 6, 15 and 48. Copying from the immediately adjacent column tests mixed references without rebuilding the model manually.
Troubleshooting and a deliberate failure
In the scenario copy only, try replacing C4 with =B4*(1-C3)*(1+B1) and copying it one column right. D4 becomes =C4*(1-D3)*(1+C1), which reads the previous result and an empty C1 instead of the base price and tax rate. It can still produce a plausible-looking number. Restore C4 and D4 using the correct explicit formulas; no Undo history across reopening is assumed.
If all products use one base price, look for $B$4 where $B4 belongs. If every discount is zero, inspect header types and row locks. If the formula is displayed as text, confirm the leading equals sign and cell format before copying again. This is reference design; formula-compatibility troubleshooting covers a different class of failures.