6. Tables, Sorting and Filtering
6.8 The Subtotal Feature
Data › Outline › Subtotal inserts subtotal rows for each group and builds an outline.
Steps in Excel
- The command does not work inside a Table: Table Design › Convert to Range first (or work on a copy).
- Sort by the grouping column (e.g. City) – the command groups adjacent rows.
- Data › Outline › Subtotal › At each change in: City › Use function: Sum › Add subtotal to: Amount › OK.
- Use the outline buttons 1 2 3 on the left: 1 = grand total, 2 = city subtotals, 3 = all details.
- Nested subtotals: run it again for Platform and untick Replace current subtotals.
- Remove: Data › Outline › Subtotal › Remove All.
Worked example (mini dataset, sorted by City). Level 2 view:
| City | Amount |
|---|---|
| Kolhapur Total | 232 |
| Nagpur Total | 240 |
| Nashik Total | 360 |
| Pune Total | 544 |
| Sambhaji Nagar Total | 195 |
| Solapur Total | 1,299 |
| Grand Total | 2,870 |
Ravindra Bagale's Tip
Subtotal command lavaychya aadhi sort karayla khup students visartat – mag ekach city che 3-4 vegle subtotals yetat. Aadhi group column var sort, mag Subtotal. Level 2 view copy karaycha asel tar Alt + ; ne visible cells copy kara.
Practice task
On a copy of the orders, create subtotals of Amount by City and nested counts by Status. Copy only the level-2 rows into a summary sheet.