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 + ; याद रखो.
Practice task
Group daily sales columns into weeks and months. Collapse to month level and print (or save as PDF) only the summary.
Thodkyaat sangaycha tar (quick recap)
Data milala ki pahila Ctrl + T aani Table la nav; structured references vachayla sope; Total Row filter pramane badalto; sort kartana poora data ekatra; AutoFilter jhatpat, Advanced Filter AND/OR sathi; filtered totals sathi SUBTOTAL, errors sathi AGGREGATE; Subtotal command aadhi sort; lapvaycha asel tar Hide nako, Group. Aata pudhe jaauya – PivotTables!