Concept
1. Date grouping
Drag Date into Rows. Modern Excel often groups automatically into Years / Quarters / Months. To control it: right-click any date → Group → choose Months (and Years if your data covers more than one year — otherwise April 2025 and April 2026 merge into one "Apr").
Practice data (one year) grouped by Months: Apr 4,08,000 · May 3,80,000 · Jun 2,82,000.
Other choices: Days (with "Number of days: 7" for weeks), Hours, Minutes, Seconds, Quarters.
To undo: right-click → Ungroup. To stop auto-grouping everywhere: File → Options → Data → tick Disable automatic grouping of Date/Time columns in PivotTables.
2. Financial year (April–March) — the Indian trap
Pivot Quarters are calendar quarters (Qtr1 = Jan–Mar). For an Apr–Mar FY, add helper columns in the source table:
FY =IF(MONTH([@Date])>=4, "FY"&YEAR([@Date])&"-"&RIGHT(YEAR([@Date])+1,2), "FY"&YEAR([@Date])-1&"-"&RIGHT(YEAR([@Date]),2))
FY Qtr ="Q"&ROUNDUP((MOD(MONTH([@Date])-4,12)+1)/3,0)
For 05-04-2026: FY = FY2026-27, FY Qtr = Q1. For 15-01-2026: FY2025-26, Q4. Use these fields instead of pivot quarters.
3. Numeric bins
Drag Amount into Rows (yes, Rows), keep Count of Amount in Values. Right-click → Group: Starting at 0, Ending at 199999, By 50000:
| Amount range | Orders |
|---|---|
| 0–49999 | 7 |
| 50000–99999 | 5 |
| 100000–149999 | 3 |
| 150000–199999 | 1 |
A quick frequency table — the basis of a histogram (Module 5).
4. Manual groups
Select items in the pivot (Ctrl + click North and West) → right-click → Group. Excel creates "Group1" and a new field "Region2". Click the Group1 label and type a better name ("North-West Zone"). The new field can be used like any other — move it to Columns or a slicer.
For permanent groupings that many reports need, a mapping column in the source (or a dimension table) is better than manual pivot groups.
5. "Cannot group that selection"
Grouping needs every value in the field to be a real date (or number). Causes:
- a blank cell in the Date column,
- dates stored as text (left-aligned, from an export),
- an error or "NA" in the column,
- the source range includes empty rows below the data.
Fix the source (Foundations M5/M6), make it a Table, then Refresh.
6. Grouping and shared cache
Pivots built from the same source share a "cache": grouping dates in one pivot groups them in the others too. If you need different grouping in two pivots, add helper columns in the source instead.
Common mistakes
Grouping by Months only across two years (months merge). Using pivot Quarters for an Indian FY. Blank or text dates blocking grouping.