Spreadsheets

Pivot table by financial year (April to March) using a helper column

Group a pivot table by Indian financial year, April to March, with a helper column for FY, fiscal quarter and month order, plus a worked example.

Editorial illustration for Pivot table by financial year (April to March) using a helper column

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.
DateFinancial yearQuarterFY month no.
12-Apr-20252025-26Q11
28-Sep-20252025-26Q26
03-Jan-20262025-26Q410
31-Mar-20262025-26Q412
01-Apr-20262026-27Q11

How do I build the pivot with these fields?

  1. 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.
  2. Choose Insert, PivotTable.
  3. Drag FY to Rows, then Category under it, or put FY in Columns if you want years side by side.
  4. Drag Quarter to Columns for a quarter view, or FYMonth for a twelve-month view.
  5. Drag Amount to Values as Sum of Amount.
  6. 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports