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 |
About this data: Teaching sample for a fictional shop “Summit Schools”. Figures and names (e.g. Meera Joshi) are made up for this lesson on the subtotal feature.
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 करो.
Worked example — the subtotal feature
Why
Neha Shah in Pune and Emma Clarke abroad both need a clear, repeatable way to use the subtotal feature on a BrightLedger Finance sheet without breaking totals.
What
The Subtotal Feature is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for the subtotal feature.
- Enter a small made-up value for Neha Shah using $ where money appears.
- Add a second value for Emma Clarke using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once the subtotal feature is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of the subtotal feature on a blank sheet for GreenField Dairy.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Meera Joshi and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Liam Murphy.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply the subtotal feature to a 5-row made-up GreenField Dairy table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with the subtotal feature.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as GreenFieldD_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
