Create Subtotals and Expandable Outlines in LibreOffice Calc

Use Data → Subtotals when you want detail rows and their summaries in the same sheet. Calc inserts summary rows and an outline that can hide or reveal the details. This changes the sheet’s structure, so start from a clean copy of the records.

Prepare deliberately unsorted orders

Use desktop Calc with the standard menu bar and an ODS working copy. The linked Help was checked directly on October 19, 2026 and labeled LibreOffice 26.8. The exercise has independent arithmetic checks, but the native dialogs, outline display and save/reopen behavior have not been executed.

Enter this table in A1:D7 on Orders:

Region	Order ID	Units	Amount
South	S-01	2	80
North	N-01	3	120
South	S-02	1	25
North	N-02	2	70
West	W-01	4	160
North	N-03	1	30

Keep an untouched copy in another workbook or on a separate Raw sheet. The six records contain 13 units and an amount total of 485. There are no blank rows or manual grand totals inside A1:D7.

1. Sort complete records and add subtotals

Select A1:D7, choose Data → Sort, and sort by Region ascending, then Order ID ascending. Ensure the range has column labels. The expected ID order is N-01, N-02, N-03, S-01, S-02, W-01. Check each ID still has its original units and amount; sorting just the region column would corrupt the records while possibly leaving the overall sum unchanged.

With the full table selected, choose Data → Subtotals. On 1st Group, set Group by to Region. Under Calculate subtotals for, select Units and Amount, assigning Sum to each. Leave 2nd Group and 3rd Group unused for this first pass. The group settings reference separates the grouping column from the columns being aggregated.

On Options, enable Pre-sort area according to groups and use ascending order. Explicitly select Summary below data for the layout described here; do not rely on the installation’s current default. Leave page breaks between groups off. Confirm. These settings are documented in Subtotal Options.

2. Reconcile details, group sums and grand total

Expect these summaries beneath the corresponding detail blocks:

Region	Detail rows	Units sum	Amount sum
North	3	6	220
South	2	3	105
West	1	4	160
Grand total	6	13	485

With only these six records and summaries below data, the detail rows should be 2–4, 6–7 and 9. The three regional summary rows should be 5, 8 and 10, with the grand summary at 11. Verify the labels rather than assuming those row numbers if you introduced blank rows or another grouping level.

Inspect a summary cell in the formula bar. The inserted summary mechanism must yield 485 overall, not 970. The Subtotals command reference describes automatic group and grand summaries.

Do not add =SUM(D2:D10) as a replacement grand total: that range contains both the original 485 of detail and 485 of regional summaries, so an ordinary SUM returns 970. A SUM including the existing grand-total cell as well would produce 1455. Keep the raw-data control total separate from the decorated report.

3. Use the outline without deleting detail

The outline controls appear beside the row headings. For one grouping level, the numbered levels normally expose the grand summary, then regional summaries, then full detail. Click the smallest level and inspect what remains visible. Move to the next level to see North, South and West; expand to the full-detail level to see all six IDs.

Use a group’s minus control to collapse just North, then its plus control to restore it. Confirm its three original IDs return with their original values. Outline hiding changes visibility, not the stored dataset. Avoid treating hidden detail as deleted or assuming a normal SUM is a visible-only total.

Save, close and reopen the ODS. Check that the grouping controls and summaries remain, expand everything, and count the six detail records again. The Calc data-analysis guide covers the relationship between grouped summaries and outline controls.

4. Edit a value, then safely rebuild for a new record

Locate N-02 by its ID, not its pre-subtotal row number. Change its Amount from 70 to 90. With automatic calculation enabled, North should become 240 and the grand total 505; South and West remain 105 and 160. Units stay 13. If the values do not update, inspect calculation settings and whether the summary cells were overwritten with constants.

For a structural change, remove the existing summaries first. Select the complete subtotaled area, reopen Data → Subtotals, and choose Delete in that dialog. This deletes the generated subtotal rows. Do not use the keyboard Delete key on an arbitrary selection, and do not merely remove the outline: removing visibility controls is not the same as removing summary rows.

Verify that exactly six detail records remain, now totaling 505. Add South, S-03, 2, 40 as a seventh record. Select the expanded table, sort complete records and repeat the subtotal setup. Expected summaries are North 240, South 145 and West 160, with 15 units and 545 overall.

Troubleshooting and aggregation choices

• Several North sections: inspect spelling, spaces, grouping order and pre-sort settings. A consistent label is the group key.
• The dialog offers the wrong headers: cancel and select the actual header-plus-record range.
• Grand total doubled: inspect whether source totals or previous generated summaries were included as records. Restore the raw copy and rebuild.
• An ID disappeared: expand all outline levels before concluding it was deleted; then compare against the raw IDs.

Sum suits quantities and monetary amounts here. It does not suit an average selling price or a percentage. If you switch to Average, North’s original amount average is 220/3, South’s is 52.5 and West’s is 160; the overall six-order average is 485/6, not the unweighted average of those three group averages. Choose the aggregation that matches the question and retain an independent detail-level calculation.