OpenOffice Calc · Data entry and editing · Beginner
Paste Values, Formats and Transposed Data in OpenOffice Calc
Freeze formula results, paste only formatting and transpose a separate table in Apache OpenOffice Calc without losing destination formulas.
Paste Special separates cell content from appearance. Here you will freeze calculated totals, copy formatting onto a formula without replacing it, and turn a small column-based table sideways.
Scope and reproducible source
Apache OpenOffice Calc 4.1 desktop, English Windows/Linux menus; documentation checked against the official 4.1 Calc Guide and versioned 4.1.6 Help on 19 October 2026. No native application run or save/reopen test was performed for this exercise.
Start a blank workbook. Enter this source in A1:D4, one cell per field. Formula separators are irrelevant to these simple multiplication formulas.
| Row | A: Item | B: Units | C: Price | D: Total |
|---|---|---|---|---|
| 1 | Item | Units | Price | Total |
| 2 | Cable | 3 | 8 | =B2*C2 |
| 3 | Clip | 4 | 5 | =B3*C3 |
| 4 | Case | 2 | 11 | =B4*C4 |
D2:D4 should calculate 24, 20, 22; the combined amount is 66. In F1 enter Snapshot, and leave F2:F4 empty. Enter Live audit in H1 and =SUM(D2:D4) in H2. Leave J1:M4 and A8:D10 empty.
Format D2 with Format → Cells → Numbers, Number category and two decimal places; make it bold. H2 can retain its default format. Save a before copy as paste-before.ods.
Freeze numbers without copying formulas
- Select D2:D4 and choose Edit → Copy.
- Select F2 and choose Edit → Paste Special. The Calc cell-selection dialog requires cells on the clipboard; text copied from a web page can open a different dialog.
- Clear Paste all. In Selection, select Numbers only. Leave Formulas, Text, Date & time, Notes/Comments, Formats and Objects clear.
- Set Operations to None, leave Skip empty cells, Transpose and Link off, and use Don’t shift.
- Confirm. Inspect F2, F3 and F4 in the input line.
Expected F2:F4 contains constants 24, 20, 22, with no leading equals sign in the input line. The destination need not display two decimals because Formats was excluded. A display of 24 rather than 24.00 is not a failed numeric paste.
Change B2 from 3 to 5. D2 becomes 40, D3:D4 remain 20, 22, and H2 becomes 82. F2:F4 must stay 24, 20, 22. That change test distinguishes a snapshot from a copied formula or a linked range. Restore B2 to 3 before continuing.
Versioned Paste Special Help documents the individual content classes, operations, Link and Transpose controls. The checked choices should be reviewed on every paste rather than assumed from a previous operation.
Apply only the source format
- Copy D2 again; copying just one source cell keeps this test unambiguous.
- Select H2, which contains the audit formula.
- Open Edit → Paste Special, clear Paste all, and select Formats only.
- Set Operations to None, turn Transpose and Link off, and choose Don’t shift. Confirm.
- Select H2 and read the input line.
H2 should still contain =SUM(D2:D4) and now display its result in bold with two decimals, normally 66.00 in a decimal-point locale. A comma locale may display 66,00. Changing B2 to 5 again should make H2 82.00, proving the formula survived. Restore B2 to 3.
Formats-only does not mean “protect the destination formula” under all paste combinations. It means no content class is selected for this paste. Selecting Numbers or Formulas as well can replace the content you intended to retain. The official Calc Guide, chapter 2, explicitly documents the Formats-only workflow.
Transpose a separate, formula-free block
Build this small source at A8:C10. It is independent of the totals example, so there are no relative-reference surprises.
| Row | A | B | C |
|---|---|---|---|
| 8 | Quarter | East | West |
| 9 | Q1 | 12 | 7 |
| 10 | Q2 | 15 | 9 |
Copy A8:C10. Select J1 and open Paste Special. Clear Paste all; select Text and Numbers, with Formulas and Formats off. Set None, Transpose on, Link off, Skip empty cells off and Don’t shift. Confirm.
Expected J1:L3 is:
| Row | J | K | L |
|---|---|---|---|
| 1 | Quarter | Q1 | Q2 |
| 2 | East | 12 | 15 |
| 3 | West | 7 | 9 |
J4:L4 and M1:M3 remain blank. Check both text labels and numbers: a totals check cannot detect an incorrect orientation. The four numbers sum to 43 in either layout. Change B9 to 99; K2 should remain 12 because Link was off. Restore B9 to 12.
Advanced variation: skipped blanks
In N1 enter Keep, N2 enter 900, N3 enter 800. In P2 enter 7 and leave P3 empty. Copy P2:P3, select N2, and paste Numbers only with Skip empty cells on, None, Transpose off, Link off and Don’t shift. Expect N2=7 and N3=800. This is a deliberate exception to the earlier settings: a source blank does not overwrite the second target cell.
Do not treat Skip empty cells as a formula-preservation feature. A nonblank source value will still replace a selected destination content type. Restore N2 to 900 manually if you want the original test state.
Recovery and final checks
Save the verified main exercise as paste-result.ods and reopen it. Check H2’s formula, F2:F4’s constants and the full J1:L3 table. These save/reopen steps remain unexecuted by the author.
If formulas appear as text, diagnose that separately with formula text instead of results. If a live destination became a number, undo immediately, check the formula, and redo Formats only. After reopening, restore from paste-before.ods instead of expecting Undo to recover a past session.
If totals were multiplied or added unexpectedly, Operations was not None. If cells moved, inspect Shift cells. If the transposed copy updates with the source, Link was enabled. If text labels disappear, Text was excluded. Preserve the original source block while diagnosing rather than cutting it away.
The values above are independently calculated expectations; the new workflow has no native execution or screenshot evidence.