6. Tables, Sorting and Filtering
6.9 Group and Outline
Steps in Excel
- Select rows (or columns) to collapse, e.g. the daily columns Jan–Mar under "Q1".
- Data › Outline › Group › Group… (Shift + Alt + →) › Rows or Columns.
- Click – to collapse and + to expand; the level numbers (1, 2) collapse all.
- Ungroup: Shift + Alt + ←. Auto Outline (Data › Outline › Group ▾ › Auto Outline) creates groups from SUM formulas automatically.
- Settings: the ↘ launcher in Data › Outline controls whether summary rows are below or above details.
Worked example. In a monthly P&L-style sheet for the Wakad store, Shahrukh groups the 12 month columns into four quarters so the manager sees only Q1–Q4 totals, expanding a quarter when needed.
Ravindra Bagale's Tip
Many students Hide rows, and then the manager who receives the report can't find the hidden data or copies it by mistake. Use Group – the + / – buttons are clearly visible. And remember Alt + ; when filtering/copying grouped rows.
Ravindra Bagale's Tip – मराठी
बरेच students rows Hide करतात आणि मग report घेणाऱ्या manager ला तो लपवलेला data सापडत नाही किंवा तो चुकून copy करतो. Group वापरा – + / – buttons स्पष्ट दिसतात. आणि group केलेल्या rows filter/copy करताना Alt + ; लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
बहुत से students rows को Hide कर देते हैं, और फिर report लेने वाले manager को छुपा data मिलता नहीं या वह गलती से copy कर लेता है. Group इस्तेमाल करो – + / – buttons साफ़ दिखते हैं. और grouped rows को filter/copy करते समय Alt + ; याद रखो.
Worked example — group and outline
Why
Aamir Khan in Pune and James O'Neill abroad both need a clear, repeatable way to use group and outline on a GreenField Dairy sheet without breaking totals.
What
Group And Outline 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 group and outline.
- Enter a small made-up value for Aamir Khan using ₹ where money appears.
- Add a second value for James O'Neill 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 group and outline 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 group and outline on a blank sheet for Pearl Textiles.
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 Emma Clarke.
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 group and outline to a 5-row made-up Pearl Textiles 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 group and outline.
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 PearlTextil_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.
