Create Subtotals and Expandable Outlines in OpenOffice Calc

Use grouped subtotal rows to move between a compact regional summary and its individual records. Keep a clean copy of the list: hiding a group is a view change, whereas generated subtotal rows change the structure of the sheet.

Start with six records

These steps use Apache OpenOffice Calc 4.1 desktop. Enter the following in A1:C7 of a new sheet. Leave the rest of the sheet empty because subtotals insert rows. Save the original as a clean ODS file before building the working report.

Row	A: Region	B: Order	C: Amount
2	West	W01	70
3	East	E01	100
4	North	N01	30
5	East	E02	50
6	West	W02	20
7	North	N02	40

The independent control total is 310. East contributes 150, North 70 and West 90. All amounts are plain numbers; no source row is itself a total.

Sort and create one grouping level

1. Select the complete A1:C7 block. Choose Data > Sort, sort by Region ascending, and confirm on Options that the range has column labels. Keep whole records together. See safe sorting if unfamiliar with the dialog.
2. Select the full sorted block and choose Data > Subtotals.
3. On 1st Group, choose Region in Group by. In Calculate subtotals for, select Amount only; choose Sum in Use function. Leave the second and third grouping tabs unused.
4. Click OK. Calc adds a subtotal for each group and a grand total. Check the three region values and the grand total against 150, 70, 90 and 310 before collapsing anything.
5. Expand all detail rows and confirm that E01 still has 100, E02 50, N01 30, N02 40, W01 70 and W02 20. Sorting may change positions; identify a record by its Order value, not its former row number.

Read the outline without double counting

The outline controls sit beside the row headers. Use a group’s minus control to hide its detail and its plus control to reveal it. Numbered controls select outline depth; for this one-level subtotal exercise, the compact level shows the grand total, the intermediate view shows regional subtotals, and the full detail view includes individual orders. Confirm the displayed labels and values rather than relying on a button number in a sheet with pre-existing groups.

Collapsing is not deletion. The grand total remains 310 at every outline depth. Do not add all visible Amount cells with an ordinary SUM: when both detail and generated totals are included, that counts the same sales more than once. Inspect a generated total’s formula in the input line and leave Calc’s subtotal formulas intact. A useful independent check is to total the six original amounts in your separate clean copy, where there are no generated rows.

Change, remove the outline, and rebuild

Expand East. Find order E02 and change its Amount from 50 to 65. East should become 165 and the grand total 325; North and West stay 70 and 90. If the formulas do not update, check Tools > Cell Contents > AutoCalculate and that the edited cell is a source Amount rather than a subtotal.

Save the working report as a different ODS file and reopen it. Expand the groups and verify those amounts again. To remove the grouping controls from the working report, choose Data > Group and Outline > Remove. This removes outline groups; it is not a promise to delete generated subtotal rows or restore the original list.

For a clean rebuild, open the untouched six-record file and save a new working copy. Append East, E03, 25 as row 8, select A1:C8, sort and create subtotals again. East is now 175 and the grand total 335. This deliberately rebuilds from raw records, avoiding the risk of feeding previous subtotal rows into a second report. It also avoids relying on Undo after closing a file.

Advanced case and troubleshooting

To explore a second grouping level, make another clean working copy with a new Channel column. Set the heading D1 to Channel. Assign Shop to W01, E01 and N01; assign Web to E02, W02 and N02, locating each row by its Order value. Select all four columns before creating this report. Group by Region on 1st Group and Channel on 2nd Group, with Amount/Sum selected for both. East’s channel summaries should be 100 and 50 in the unchanged original fixture, nested inside its 150 regional total. This extra level changes the outline depth, so validate labels when collapsing.

Repeated region groups usually indicate inconsistent spellings or a grouping/sorting setup that did not collect like labels. An unexpected Count instead of Sum indicates the wrong function. If dates or order codes are totaled, revisit Calculate subtotals for. Never repair a wrong total by typing over its formula; restore a clean copy and fix the input or grouping.

The official 4.1 Data Analysis chapter documents Data > Subtotals and its controls. Outline Group Controls in the 4.1 Calc Guide documents plus/minus controls, depth buttons and removal. On macOS, preferences are under OpenOffice > Preferences instead of Tools > Options where settings are needed. No native run or screenshot of this fixture is claimed; the numerical checks are independent expectations, and exact build-specific outline presentation remains to be checked locally.