WPS Spreadsheet · Workbook workflows · Beginner
Create subtotals and collapsible groups in WPS Spreadsheet
Summarize sorted category records, reconcile totals and distinguish automatic summaries from manual row groups.
Documentation-based procedure; synthetic checks independently verified; no native application execution · Editorial standards
Quick answer: Start with contiguous records organized by category, then choose Data > Categorical Summary and summarize the Amount column by Sum. Collapse the outline to review category totals, and expand it before checking individual records. Manual row grouping changes visibility, not arithmetic.
Prerequisites and labels
This guide follows the Windows Docs Center interface, where the summary command is Categorical Summary in the Data tab’s Outline group. Some Academy material calls a related workflow Subtotal; do not combine dialog paths from different interfaces. The documentation does not identify an installed numeric build, and this is not a browser or mobile procedure.
Use a disposable workbook with a header row, no merged cells and no blank rows inside the records. The fixture below is already sorted into contiguous category blocks. For your own data, sort the entire record range by its grouping field first; moving only one column can separate amounts from their descriptions.
Enter the sorted report
Put this data in A1:C6:
| Category | Item | Amount |
|---|---|---|
| Meals | Breakfast | 12 |
| Meals | Lunch | 18 |
| Travel | Bus | 8 |
| Travel | Train | 22 |
| Travel | Taxi | 10 |
The control totals are Meals = 30, Travel = 40 and overall = 70. There are five detail records. Save a copy before generating summary rows so you can compare the result with the unmodified input.
Generate and inspect the summary
- Select a cell inside A1:C6.
- Choose Data > Categorical Summary in the Outline group.
- Set the grouping field to Category and the summary method to Sum.
- Select Amount as the column to calculate. Leave Summary below data selected for this exercise.
- Confirm the dialog. Inspect the generated category summary rows and total structure.
- Use the outline controls to collapse the detail and read the summaries. Confirm 30 for Meals, 40 for Travel and 70 overall.
- Expand the outline and count the original five records. Breakfast must still be paired with 12, and Taxi with 10.
Do not add every visible number in the expanded report to verify the total: doing so can count detail values and their generated summaries twice. Instead, reconcile the five original Amount values separately against the overall total.
Change the report and rebuild deliberately
Find the Taxi detail row by its label rather than assuming its row number remained 6 after summary rows were inserted. Change its Amount from 10 to 16. The required control totals are now Meals 30, Travel 46 and overall 76. Inspect what the existing report shows, but do not assume every kind of source change will be incorporated automatically.
For a reproducible refresh, click inside the summarized range, reopen Categorical Summary, and choose Remove All, then confirm. The Windows guide describes this as removing generated summary rows and outline structure without changing the original details. Check that all five detail records remain, including Taxi 16. Run the same summary again and verify 30, 46 and 76.
This remove-and-rebuild checkpoint is especially useful after adding categories or records. It avoids layering a new summary on an old one by mistake. Multi-level nested summaries are a different task and are not necessary for this report.
Compare manual grouping
First save the summarized main workbook with Meals 30, Travel 46 and overall 76. Keep that file as your completed report. Open a separately named copy of the original unsummarized fixture for the following visibility experiment, and select the contiguous Meals detail rows 2:3. Choose Data > Group, select Rows, and confirm. Collapse and expand the group. The data remains unchanged, and no category total has been calculated merely by creating the group.
To reverse this, place the cursor inside the grouped area and choose Data > Ungroup. Do not confuse these row controls with grouping worksheet tabs: grouped worksheets can receive synchronized edits, which is not what this exercise requires.
Troubleshooting and saving
Repeated category totals often mean the category records were not contiguous. Missing records suggest a blank row, wrong selection or merged-cell issue. An incorrect sum can also mean an Amount entry is stored as text. Fix the source, remove the generated structure and rebuild rather than editing a displayed summary to conceal the problem.
Close the manual-group experiment and reopen the saved summarized main workbook. Expand its outline and confirm five original detail records, including Taxi 16, then collapse it and confirm totals of 30, 46 and 76. Collapsed rows are still present; a compact view is not a reduced dataset.
Related guides
Use number and date formatting to inspect numeric inputs. For a different aggregation layout, see PivotTable basics.
Official references
- WPS Docs Center: Categorical Summary on Windows (source captured 2026-10-20).
- WPS Docs Center: create and remove groups on Windows (source captured 2026-10-20).