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:

DOWNLOAD EXCEL 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:

=CHOOSE(MONTH(E2),3,3,3,4,4,4,1,1,1,2,2,2)

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:

=CHOOSE(MONTH(E2),3,3,3,4,4,4,1,1,1,2,2,2)

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

 

HELPFUL RESOURCE:

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

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn