Insert, Delete or Clear Cells Safely in LibreOffice Calc

Clear a value when its cell should stay in place. Insert or delete cells when surrounding positions should change. A whole-row operation moves every column in that row; shifting only one column can detach quantities from their item labels.

This tutorial uses LibreOffice Calc’s English standard menus on Windows/Linux, following Calc Guide 26.2, Chapter 1 and Help labelled 26.8, checked 23 October 2026. The small arithmetic and record transformations were checked independently. No native dialog, reference rewrite, save/reopen result or screenshot is claimed.

Build a disposable record table

Use a new ODS workbook with a single selected sheet. You can enter the table manually or download the structural-edit practice CSV. The original CSV has a header and three synthetic records, no formulas, formatting, macros or external links. Import it with comma separation, check A1:C4, then save an ODS copy. Add E1 and the E2 formula below yourself; CSV does not contain them.

Enter or verify:

Cell	Entry
A1	Item
B1	Units
C1	Zone
A2	Box A
B2	4
C2	North
A3	Box B
B3	7
C3	South
A4	Box C
B4	9
C4	West
E1	Total units
E2	=SUM(B2:B4)

Expect E2 to show 20. Add a visible fill to B3 so you can distinguish removing a number from removing its format. Save a practice copy before the experiments. Do not use a filtered, merged or production table for this first exercise.

Check the sheet tabs as well as the selection rectangle. Clear Cells Help warns that clearing with multiple sheets selected affects all selected sheets.

Clear only the quantity

Select B3 and choose Sheet → Clear Cells. Clear the Delete All choice and select Numbers only, leaving other categories unselected. Confirm the operation.

Expected state: B3 is empty, its fill remains, and Box B and South still occupy A3 and C3. E2 should now be 13, because 4 + 9 = 13. This result does not mean Box B has been removed or its quantity is known to be zero: the record now has a missing quantity.

Undo immediately and verify B3 = 7 and E2 = 20 before continuing. Selecting Formats instead would be a different operation; do not select it merely because the blank cell still looks colored. The documented Delete key clears contents without changing formatting, whereas Backspace opens the choice dialog. Using the named menu command makes this experiment less dependent on key assignments.

See why a one-cell shift can corrupt a record

Starting again from the original table, select B3 only and choose Sheet → Delete Cells. Explicitly choose Shift cells up. Do not accept a remembered dialog choice without reading it: Delete Cells Help says the selected option is retained for next time.

The value 9 moves from B4 to B3, while A3 remains Box B and C3 remains South. You have associated Box C’s quantity with Box B’s label. A plausible numerical total cannot detect this relationship error. Inspect A2:C4 before doing anything else, then Undo and verify all three complete records.

Deleting a whole row is different. Select the row 3 header, choose Sheet → Delete Rows, and inspect the remaining records: Box A/4/North and Box C/9/West. Their quantities sum to 13. Inspect E2’s formula and result rather than assuming every dependent formula, named range or chart has followed your intended business rule. Undo and confirm the original table and total.

Insert a complete record, not one isolated cell

Select the row 3 header and choose Sheet → Insert Rows → Rows Above. A blank row should appear between Box A and Box B. In the new A3:C3 enter Box D, 6, East. The original Box B and Box C records should now be on rows 4 and 5, still aligned across all three columns.

The intended total is 26 = 4 + 6 + 7 + 9. Inspect E2 and its range. The intended range now covers B2:B5. If the displayed formula or result does not match, stop and investigate the selected operation and inputs; do not overwrite E2 with the constant 26.

The reference documentation explains that references adjust when referenced cells move, including absolute references. Dollar signs control copying behavior; they are not a promise that a structural insertion leaves an address unchanged. This example uses an interior insertion, not an insertion just outside a range boundary, where inclusion needs a separate check.

Undo the entered cells and inserted row, or close this unsaved practice variant and reopen your known original copy. Check the full table and formula before the next experiment.

Match the selection to the operation

Insert Cells Help distinguishes shifting selected cells down or right from inserting entire rows or columns. The selection determines both position and quantity. Selecting two row headers before inserting adds two rows, not one.

Before confirming a structural edit, ask:

• Does every column in this record need to move together?
• Are other tables or notes farther right in the same row affected?
• Is only one sheet selected?
• Which formulas, names, charts or totals depend on this area?

Use an entire row only when its whole-sheet effect is intended. A local table beside another table may require a carefully bounded block operation instead. Do not assume that deleting only visible filtered cells is the same as this unfiltered exercise.

Finish with a reference and content check

Save, close and reopen your chosen practice result. Check labels, quantities and zones together, then inspect the formula rather than only its answer. This is an acceptance test for the reader, not a reported native test performed here.

For reference-copying behavior use absolute and relative references. For larger record ranges use named and database ranges. Those are separate topics; neither substitutes for checking what the current selection will move or erase.