Excel pivot grouping follows calendar years and quarters, so an April to March financial year needs a helper column. Add a column that returns a label like 2025-26 from each date, and use that column as a pivot field next to Category. A second helper gives the fiscal quarter, so Q1 is April to June.
What formula returns the financial year for a date?
With the date in A2, this formula gives labels such as 2025-26: =IF(MONTH(A2)>=4,YEAR(A2)&"-"&RIGHT(YEAR(A2)+1,2),YEAR(A2)-1&"-"&RIGHT(YEAR(A2),2)). Dates from April onward take the current year as the start, and January to March take the previous year as the start. So 15 August 2025 returns 2025-26, and 10 February 2026 also returns 2025-26.
If you prefer a shorter label, ="FY"&YEAR(EDATE(A2,9)) shows the year in which the financial year ends. EDATE adds nine months, so April 2025 becomes January 2026 and returns FY2026; March 2026 becomes December 2026 and also returns FY2026. Pick one style and stay with it.
How do I add the fiscal quarter and month number?
- Fiscal quarter in column D: ="Q"&(INT(MOD(MONTH(A2)-4,12)/3)+1). April to June gives Q1, July to September Q2, October to December Q3, January to March Q4.
- Month in financial order in column E: =MOD(MONTH(A2)-4,12)+1. April becomes 1 and March becomes 12.
- Month name in column F: =TEXT(A2,"mmm"). Use it for display, with column E for ordering.
| Date | Financial year | Quarter | FY month no. |
|---|---|---|---|
| 12-Apr-2025 | 2025-26 | Q1 | 1 |
| 28-Sep-2025 | 2025-26 | Q2 | 6 |
| 03-Jan-2026 | 2025-26 | Q4 | 10 |
| 31-Mar-2026 | 2025-26 | Q4 | 12 |
| 01-Apr-2026 | 2026-27 | Q1 | 1 |
How do I build the pivot with these fields?
- Format the data as a Table with Ctrl+T, and type the helper formulas in new columns inside the Table so they fill down on their own. Name the headers FY, Quarter and FYMonth.
- Choose Insert, PivotTable.
- Drag FY to Rows, then Category under it, or put FY in Columns if you want years side by side.
- Drag Quarter to Columns for a quarter view, or FYMonth for a twelve-month view.
- Drag Amount to Values as Sum of Amount.
- Use the FY drop-down to show one year at a time, or leave all years visible to compare.
Why not just group the date field?
The Group dialog offers Years, Quarters and Months of the calendar. It has no setting for a year starting in April, so January to March of one year would appear next to April to December of the same calendar year. A tax-time summary or a household review that follows April to March would then be split in the wrong place. The helper column costs one formula and gives you the split you actually need.
A worked check you can run
Say a hypothetical log has these Category totals: ₹3,60,000 spent in 2025-26 from April 2025 to March 2026, of which ₹92,000 fell in Q4. The pivot should show 3,60,000 under 2025-26 and 92,000 under Q4. Add the four quarters, and the sum must equal the year total. If a few rows show 2024-25 or a blank, their dates are text or fall outside your data.
What should I do next?
Keep the helper columns even after the first pivot, since you will reuse them for every report. If you prepare figures for a CA, give them the pivot as a record of your own spending, and let them tell you how to treat it; this article is about arranging records, not about tax. SpendixAI is another way to keep a whole year in one place, since you can import statements and see category totals across periods, which saves rebuilding helper columns each April.
- FY helper column added inside the Table.
- Quarter and month number columns added.
- Pivot uses FY, Quarter or FYMonth as fields.
- Quarter totals add up to the year total.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

