LibreOffice Calc · Data entry and editing · Intermediate
Paste Values, Formats and Transposed Data in LibreOffice Calc
Use Calc Paste Special to freeze formula results, copy formatting without replacing formulas, and transpose a clear block with an auditable destination.
Documentation-qualified worked recipe; new native workflow not executed · Editorial standards
Edit → Paste Special → Paste Special lets you choose which parts of copied Calc cells reach the destination. A values-only paste is a snapshot; a formats-only paste should leave the destination’s contents intact. Neither choice protects unrelated cells if you selected the wrong destination.
Use a disposable ODS workbook for this exercise. The commands follow Calc Guide 26.2 and current Help labeled 26.8, checked on 19 October 2026, in the English standard-menu interface. The new example has not been executed in native Calc. Its arithmetic and coordinate mappings are independent expected controls.
Set up source cells and a protected-by-position report
On a sheet named Practice, enter this source in A1:D4. Use ordinary numbers, not currency symbols typed into the cells.
| Row | A: Item | B: Units | C: Price | D: Amount |
|---|---|---|---|---|
| 2 | Folder | 4 | 9 | =B2*C2 |
| 3 | Binder | 3 | 14 | =B3*C3 |
| 4 | Label | 5 | 6 | =B4*C4 |
Expected source amounts are 36, 42 and 30, totaling 108. Apply bold to A1:D1 and use Format → Cells → Numbers to display D2:D4 with two decimal places. Give D2:D4 a pale background using Format → Cells → Background. This deliberately distinguishes the source formatting from the report.
Enter the following destination controls:
| Cell | Entry |
|---|---|
| F1 | Snapshot |
| F2:F4 | Leave empty |
| F5 | =SUM(F2:F4) |
| G1 | Live report |
| G2 | =D2 |
| G3 | =D3 |
| G4 | =D4 |
| G5 | =SUM(G2:G4) |
| H2 | DO NOT REPLACE |
Set F2:F4 to Number with zero decimal places and no colored background. Save the workbook now. F5 lies outside the three-row paste target; H2 is a sentinel for an accidentally oversized paste.
1. Paste formula results as a static snapshot
- Select D2:D4 and copy it with Edit → Copy.
- Select F2, the upper-left destination. The footprint is F2:F4.
- Choose Edit → Paste Special → Paste Special. Clear Run immediately before choosing a preset, so you can inspect it without executing early.
- Choose Values Only. Confirm that Formulas and Formats are not selected, Operation is None, Shift Cells is Don’t shift, and As Link and Transpose are off. Keep Skip empty cells off for this fully populated source.
- Click OK. Inspect F2:F4 in the formula bar. They should contain numbers 36, 42, 30, with no leading equals sign; F5 should be a formula returning 108. The destination’s zero-decimal formatting should remain.
The Calc Paste Special help describes these presets and controls. This dialog is for a clipboard containing spreadsheet cells. Text copied from a website may instead invoke a text-import or generic clipboard dialog; do not assume it offers the same component selection. For text identifiers, see preserve leading zeros.
2. Copy only the formatting into a live report
Copy D2:D4 again and select G2. Open Paste Special, leave Run immediately off, and choose Formats Only. Confirm that only Formats is selected under Selection, Operation is None, Shift Cells is Don’t shift, and the link/transpose options are off. Click OK.
G2:G4 should now resemble D2:D4, including the two-decimal display and background, but retain =D2, =D3, =D4. G5 should remain =SUM(G2:G4). A visually correct report is insufficient evidence: inspect the formulas directly.
Use this audit outside both paste areas:
| Cell | Formula | Expected |
|---|---|---|
| J7 | =ISFORMULA(F2) |
FALSE |
| J8 | =ISFORMULA(G2) |
TRUE |
| J9 | =ISFORMULA(F5) |
TRUE |
| J10 | =ISFORMULA(G5) |
TRUE |
The ISFORMULA function checks storage type. It does not prove that a formula refers to the intended cells, so still inspect the reference text.
Change B3 from 3 to 4. D3 and G3 should become 56; G5 becomes 122. F3 stays 42 and F5 stays 108. Restore B3 to 3, then check both totals are 108 again. If the snapshot changed, inspect for a formula or a link. If the live report did not change, inspect whether values were pasted over its formulas.
3. Transpose a separate block without formula-reference surprises
Use constants for this operation so the shape change can be checked independently of formula relocation. Enter J1:L2 as follows:
| J | K | L |
|---|---|---|
| North | South | West |
| 8 | 11 | 6 |
Clear only the intended destination J13:K15 if it contains earlier practice data. Copy J1:L2 and select J13. In Paste Special choose Values Only with Run immediately off, then enable Transpose. Confirm Operation None, Don’t shift, As Link off and Skip empty cells off; apply.
The expected output is J13=North, K13=8; J14=South, K14=11; J15=West, K15=6. A two-row, three-column source becomes a three-row, two-column result. =SUM(K13:K15) should return 25. J16 and L13 should remain untouched. Do not use Transpose All here: that broader preset would copy additional components that this exercise does not need.
For an advanced check, change K2 to 12. K14 should stay 11 because the transposed result is a snapshot. Recopy J1:L2 and repeat the same paste to J13: expect K14=12 and total 26. Restore K2=11 and repeat to restore 25.
Recover safely and distinguish blank-cell behavior
If any paste lands in the wrong place, use Edit → Undo immediately, before further edits, then check F5, G5 and H2. If the undo history is unclear, close the practice copy without saving and reopen the checkpoint. Rebuild only the intended report area.
Skip empty cells means empty source cells do not replace destination contents. It is not a rule to preserve destination formulas generally: a nonempty source can still replace them. Mathematical operations such as Add or Multiply also change the meaning of the paste. This tutorial deliberately uses None; do not leave an operation from a previous job selected.
Finally save and reopen the ODS file. Inspect F2, G2, both total formulas, H2 and the transposed block. If Formats Only seems ineffective, compare actual cell formatting rather than column widths, which are not the component being tested here. The Calc Guide editing chapter documents the complete workflow. Save/reopen and native component-preservation results for this fixture remain untested.