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
Many students forget to sort before using the Subtotal command – then the same city gets 3–4 separate subtotals. Sort on the group column first, then Subtotal. If you want to copy the level 2 view, copy the visible cells with Alt + ;.
Ravindra Bagale's Tip – मराठी
Subtotal command लावण्याआधी sort करायला बरेच students विसरतात – मग एकाच city चे 3-4 वेगळे subtotals येतात. आधी group column वर sort, मग Subtotal. Level 2 view copy करायचा असेल तर Alt + ; ने visible cells copy करा.
Ravindra Bagale's Tip – हिंदी
Subtotal command लगाने से पहले sort करना बहुत से students भूल जाते हैं – फिर एक ही city के 3-4 अलग subtotals आ जाते हैं. पहले group column पर sort, फिर Subtotal. Level 2 view copy करना हो तो Alt + ; से visible cells copy करो.
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.