7. PivotTables and PivotCharts
7.5 Grouping Dates and Numbers
Group numbers into bands in a PivotTable with example — in 30 seconds
What: Grouping puts numbers into equal bands, like ₹0–199 and ₹200–399, directly inside the pivot.
Why: Basket-size bands show whether customers buy small top-ups or big baskets, without a helper column.
Tiny example: With bands of 200, five orders fall in 0-199 and four in 200-399. One order (₹1,299) sits in 1200-1399.
Group numbers into bands in a PivotTable with example — 30 सेकंदात
काय: Grouping मुळे numbers ₹0–199, ₹200–399 सारख्या समान bands मध्ये pivot मध्येच जातात.
का: Basket-size bands मुळे customers छोटी top-up घेतात की मोठी basket, ते helper column शिवाय दिसतं.
छोटं उदाहरण: 200 च्या bands मध्ये पाच orders 0-199 मध्ये आणि चार 200-399 मध्ये येतात. एक order (₹1,299) 1200-1399 मध्ये आहे.
Group numbers into bands in a PivotTable with example — 30 सेकंड में
क्या: Grouping से numbers ₹0–199, ₹200–399 जैसे बराबर bands में pivot के अंदर ही चले जाते हैं.
क्यों: Basket-size bands से पता चलता है कि customers छोटा top-up लेते हैं या बड़ी basket, बिना helper column के.
छोटा उदाहरण: 200 के bands में पाँच orders 0-199 में और चार 200-399 में आते हैं. एक order (₹1,299) 1200-1399 में है.
First look at the data (before → after):
Expected result — Pivot grouped by Amount band
| Row Labels | Count of Order ID | Sum of Amount |
|---|---|---|
| 0-199 | 5 | 609 |
| 200-399 | 4 | 962 |
| 1200-1399 | 1 | 1,299 |
| Grand Total | 10 | 2,870 |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Rohan Patil) are made up for this lesson on grouping dates and numbers.
Before table and formula as text
Before — Amounts in tblMini
| Order ID | Amount |
|---|---|
| BLK-1001 | 64 |
| BLK-1002 | 270 |
| AMN-1003 | 110 |
| BLK-1004 | 240 |
| AMN-1005 | 232 |
| BLK-1006 | 150 |
| BLK-1007 | 1299 |
| AMN-1008 | 195 |
| BLK-1009 | 220 |
| BLK-1010 | 90 |
About this data: Demo table only — not real customer or company data. Built to practise grouping dates and numbers.
Formula / click path
Rows: Amount › right-click › Group…
Starting at: 0 · Ending at: 1399 · By: 200
Do it step by step:
- Make a pivot with Amount in Rows.
- Add Order ID to Values (Count) and Amount to Values (Sum).
- Right-click any amount in the Rows area and choose Group….
- Set Starting at 0, Ending at 1399 and By 200. Click OK.
- Check: the counts add up to 10 and the sums to 2,870.
स्टेप बाय स्टेप करून बघा:
- Amount Rows मध्ये टाकून pivot बनवा.
- Order ID Values मध्ये (Count) आणि Amount Values मध्ये (Sum) टाका.
- Rows मधल्या कोणत्याही amount वर right-click करा आणि Group… निवडा.
- Starting at 0, Ending at 1399 आणि By 200 टाका. OK करा.
- Check करा: counts ची बेरीज 10 आणि sums ची बेरीज 2,870.
स्टेप बाय स्टेप करके देखें:
- Amount को Rows में डालकर pivot बनाएँ.
- Order ID को Values में (Count) और Amount को Values में (Sum) डालें.
- Rows की किसी भी amount पर right-click करें और Group… चुनें.
- Starting at 0, Ending at 1399 और By 200 डालें. OK करें.
- Check करें: counts का जोड़ 10 और sums का जोड़ 2,870.
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.
Loading practice tasks...
- Total nights (Easy): Add up the "Nights" column. (sheet "Data")
- Average rate per night (Easy): Average of the Rate per night (£) column, rounded to 2 decimals. (sheet "Data")
- Rows in Standard (Easy): How many rows have Room type "Standard"? (sheet "Data")
- Largest nights (Easy): The largest value in the "Nights" column. (sheet "Data")
- Days taken (Easy): the "City" value in the first row minus the "Room type" value in the first row. (sheet "Dates")
- Start year (Easy): Year of the "Room type" value in row 3. (sheet "Dates")
- Due month (Easy): Month number of the "City" value in row 3. (sheet "Dates")
- Earliest start (Easy): Earliest date in the "Room type" column (as a date number). (sheet "Dates")
- Nights for Standard (Medium): Total of the Nights column where Room type is "Standard". (sheet "Data")
- Standard in Glasgow (Medium): Total of the Nights column where Room type is "Standard" and City is "Glasgow". (sheet "Data")
- Working days (Medium): Working days from the "Room type" value in the first row to the "City" value in the first row. (sheet "Dates")
- Weekday of due (Medium): Weekday number of the "City" value in row 4 (Sunday = 1). (sheet "Dates")
Worked example — grouping dates and numbers
Why
Meera Joshi in Pune and Carlos Rivera abroad both need a clear, repeatable way to use grouping dates and numbers on a NovaCraft Tools sheet without breaking totals.
What
Grouping Dates And Numbers 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 grouping dates and numbers.
- Enter a small made-up value for Meera Joshi using € where money appears.
- Add a second value for Carlos Rivera 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 grouping dates and numbers is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
