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
The dates won't group and you get the message "Cannot group that selection" – because one date in the column is text or blank. Many students think it's a PivotTable problem; the real problem is in the source data. Clean the date column (5.7), fill or filter out the blanks, then group. And if you have several years of data, select Years along with Months, otherwise March of two years gets combined.
Ravindra Bagale's Tip – मराठी
Date group होत नाही, "Cannot group that selection" असा message येतो – कारण column मध्ये एखादी date text आहे किंवा blank आहे. बऱ्याच students ना वाटतं की PivotTable चा problem आहे; खरा problem source data चा असतो. Date column clean करा (5.7), blanks भरा किंवा filter करा, मग group करा. आणि अनेक वर्षांचा data असेल तर Months सोबत Years पण select करा, नाहीतर दोन वर्षांचे March एकत्र येतात.
Ravindra Bagale's Tip – हिंदी
Date group नहीं होती और "Cannot group that selection" message आता है – क्योंकि column में कोई date text है या blank है. बहुत से students को लगता है कि PivotTable की problem है; असली problem source data में होती है. Date column clean करो (5.7), blanks भरो या filter करो, फिर group करो. और कई सालों का data हो तो Months के साथ Years भी select करो, वरना दो सालों के March एक साथ जुड़ जाते हैं.
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.