In Excel, right-click any date in the pivot table, choose Group, and select both Months and Years before pressing OK. In Google Sheets, right-click a date in the pivot, choose Create pivot date group, and pick a month option. Selecting Years as well as Months matters, because months alone add January 2026 and January 2027 together.
How do I group dates in an Excel pivot table?
- Build the pivot with Date in Rows or Columns and Amount in Values. If Excel has already grouped the dates for you, you will see Months or Years as extra fields; you can still adjust them.
- Right-click any date cell inside the pivot, not the source data, and choose Group.
- In the Grouping box, the Starting at and Ending at values fill in from your data. Leave them unless you want to cut off part of the data.
- Hold Ctrl and click Months and Years so both are highlighted. Add Quarters if you want that too.
- Press OK. The pivot now has a Years field and a Months field. Drag Years above Months, or into Columns, to arrange the layout you want.
Why must I group years separately from months?
Take a hypothetical case. You spent ₹4,000 on electricity in January 2026 and ₹4,600 in January 2027. If only Months is ticked, the pivot shows a single January row of ₹8,600, which looks like one expensive month. With Years ticked as well, you get two lines, one per year, and the comparison is real. Whenever your data covers more than twelve months, tick Years.
| Grouping ticked | What January shows | Reads as |
|---|---|---|
| Months only | ₹8,600 | Two Januaries merged, misleading |
| Years and Months | 2026: ₹4,000, 2027: ₹4,600 | Each year kept apart |
| Quarters and Years | Jan to Mar totals per year | Coarser, useful for trends |
How do I group dates in Google Sheets?
- Create the pivot table from Insert, Pivot table, with Date added under Rows.
- Right-click a date value in the pivot table itself.
- Choose Create pivot date group and pick one of the options, such as Month, Quarter or Year. If your menu offers a combined year and month choice, pick that to keep years apart.
- If you only see Month, add a second date group for Year and place it above Month in Rows.
- To remove a grouping, right-click the grouped field and choose Ungroup or edit it in the editor panel.
Sheets keeps the original Date column available as well, so you can drag it back in if you want to see each day.
What if Excel says it cannot group that selection?
- The Date column contains text. Dates imported from a CSV often look right but are stored as text; convert them with Data, Text to Columns, Date, or multiply by 1 in a helper column.
- The Date column has blank cells. Fill them or delete the empty rows from the source.
- The pivot was built from a range where one cell holds a heading or a note such as Opening balance.
- The field is already grouped in another pivot sharing the same cache. Ungroup it there first, or ungroup and regroup.
- You right-clicked a value instead of a date label.
Which grouping suits expense tracking?
Months with Years suits most budgets, because you compare this month with the same month a year back. Quarters suit festival-heavy patterns, for example Diwali shopping against a quiet summer. Days are useful for one month only. If you report by financial year, April to March, plain calendar grouping is not enough, and a helper column is the cleaner route.
What should I do next?
After grouping, sort Years in ascending order, so the oldest year comes first, and format the Amount values with a thousands separator. If dating is a nuisance because every bank exports differently, a tool that reads statements for you takes that off your plate. SpendixAI lets you upload statements as CSV, Excel or PDF and review the rows before saving, so dates are checked at import and not discovered later inside a pivot.
- Confirm every date is a real date with ISNUMBER.
- Tick Years along with Months in Excel.
- In Sheets, check whether your menu has a combined year and month option.
- Refresh the pivot after adding new rows.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


