Group by Fiscal Years & Quarters | MyExcelOnline

Grouping by calendar years and quarters is straightforward as this is provided out of the box. What if you want to segregate data and group by fiscal years and quarters? Luckily there is a solution which will ease your work  –

Let us have this setup in our Pivot Table data:

  • The Fiscal Year starts in July
  • Fiscal Quarters:
    • 1st Quarter – July, August, September
    • 2nd Quarter – October, November, December
    • 3rd Quarter – January, February, March
    • 4th Quarter – April, May, June

I will show you how we can set this up!

Exercise Workbook:


We will be using the help of formulas to add two columns to our data source: Fiscal Year and Fiscal Quarter.

Before moving forward let’s get acquainted with the Excel formulas that we will be using here – YEAR(), MONTH() and CHOOSE().

  • YEAR() – This formula extracts the YEAR from the date
  • MONTH() -This formula extracts the MONTH from the date
  • CHOOSE() – This formula extracts a  specific value from the list based and the position provided.


STEP 1: Let us start with the group by Fiscal Years first. Since our fiscal year starts in July, then our formula will be:

=YEAR(E2) + (MONTH(E2) >= 7)

If the month is July or later, then we add one to the year. That will get us the next year as the Fiscal Year.

For example, December 21, 2012, will have the fiscal year of 2013, as it is July or later.

Group by Fiscal Years & Quarters | MyExcelOnline

STEP 2: Let us work on the Fiscal Quarter next. Since our first fiscal quarter starts in July, then our formula will be:


This formula will simply match the Month number to the sequence of:

  • 3rd Quarter – January (3), February (3), March (3)
  • 4th Quarter – April (4), May (4), June (4)
  • 1st Quarter – July (1), August (1), September (1)
  • 2nd Quarter – October (2), November (2), December (2)

For example, December 21, 2012 will have a fiscal quarter of 2 as it will match the last part of the formula:


Group by Fiscal Years & Quarters | MyExcelOnline

STEP 3: Once the columns are ready, right-click on your Pivot Table and select Refresh

Group by Fiscal Years & Quarters | MyExcelOnline

STEP 4: The new Fiscal columns are now there, drag the FISCAL YEAR and FISCAL QUARTER to Rows. Then drag SALES to Values

Group by Fiscal Years & Quarters | MyExcelOnline

You now have your Pivot Table ready and group by Fiscal Years and Quarters!

Group by fiscal years


Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:


Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn