Drop daily dates into a pivot and group them into months, quarters, or years — Excel does the bucketing for you. Right-click a date in the pivot and choose Group.
The example
Daily sales rolled up to monthly totals.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | $28,000 |
| 3 | Feb | $31,500 |
The formula
Grouping is a right-click action, not a formula:
How it works
Pivot date grouping in four clicks:
- Build the pivot with the date field in Rows and the value in Values.
- Right-click any date in the pivot and choose Group.
- Pick Months (add Quarters/Years for a drill-down hierarchy), then OK.
- Excel buckets the dates and totals each period. Ungroup the same way to revert.
Formula equivalent on raw data: =SUMIFS(sales, dates, ">="&startOfMonth, dates, "<="&EOMONTH(startOfMonth,0)) totals a month without a pivot — handy when you need the monthly figure inside another formula.
Try it: interactive demo
Daily entries “m/d,amount” → monthly totals.
Variations
Quarters too
Group dialog → select Quarters + Years.
Formula: month total
On raw data:
By week
Group → Days, set 7-day interval.
Pitfalls & errors
Dates must be real dates. Text that looks like a date won’t group — convert it first (DATEVALUE).
Group affects all pivots sharing the cache. Grouping in one pivot can change another built on the same data — expected behavior.
Blank/error dates block grouping. Clean the date column, or Excel refuses to group.
Practice workbook
Frequently asked questions
How do I group a PivotTable by month in Excel?
What's the formula equivalent of monthly grouping?
Why won't my pivot group the dates?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class