Pinterest Pixel

Group by Fiscal Years & Quarters

Grouping by calendar years and quarters is straightforward as this is provided out of the box. What... read more

Download Excel Workbook
Bryan
Posted on

Overview

Group by Fiscal Years & Quarters | MyExcelOnline 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 workbookGroup-by-fiscal-years-quarters.xlsx


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.
See also  2 Quick Ways to Group Time in Excel Pivot Tables

 

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

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:

See also  FILTER Function in Microsoft Excel - The Easy Way

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

Group by Fiscal Years & Quarters

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

Group by Fiscal Years & Quarters

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

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

Group by Fiscal Years & Quarters

 

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

If you like this Excel tip, please share it
Group by Fiscal Years & Quarters | MyExcelOnline Group by Fiscal Years & Quarters | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...