7. PivotTables and PivotCharts
7.5 Grouping Dates and Numbers
Steps in Excel
- Dates: drag Order Date into Rows. Excel 2016+ may auto-group into Years/Quarters/Months. To choose yourself: right-click a date › Group… › select Months and Years (for multi-year data always include Years) › OK.
- Weeks: Group › select only Days › Number of days
7› set Starting at to a Monday. - Numbers: drag Amount into Rows › right-click › Group… › Starting at 0, Ending at 1500, By 250 → bands 0–249, 250–499, …
- Manual groups of text items: select Pune, Nashik and Sambhaji Nagar labels (Ctrl-click) › right-click › Group → "Group1" › rename to Western Maharashtra (for example).
- Ungroup: right-click › Ungroup.
- Turn off auto date grouping: File › Options › Data › tick Disable automatic grouping of Date/Time columns in PivotTables.
Worked example. Ravindra Bagale wants monthly sales for FY 2026-27 with the Ganeshotsav (September) and Diwali (November) spikes visible: Rows = Order Date grouped by Years and Months, Columns = Platform. September and November stand out immediately.
Ravindra Bagale's Tip
Date group hot nahi, "Cannot group that selection" asa message yeto – karan column madhe ekhadi date text aahe kiwa blank aahe. Khup students la vatata PivotTable cha problem aahe; khara problem source data cha asto. Date column clean kara (5.7), blanks bhara kiwa filter kara, mag group kara. Ani anek varshancha data asel tar Months sobat Years pan select kara, nahitar don varshanche March ekatra yetat.
Practice task
Group orders by Month and Year, then by 7-day weeks starting on a Monday. Create amount bands of ₹250 and count orders in each band.