Group by Fiscal Years & Quarters

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  –

Key Takeaways

• Pivot Tables Make Grouping Easy – You can group dates into Fiscal Years and Quarters using Excel’s Pivot Table Grouping feature.

• Define a Custom Fiscal Year Start – Since fiscal years don’t always start in January, you can adjust groupings based on your company’s fiscal calendar (e.g., July–June).

• Use Helper Columns for Fiscal Grouping – If the Pivot Table method doesn’t work, create a helper column with formulas to assign Fiscal Year and Quarter labels.

• Custom Formulas for Fiscal Years – The formula =YEAR(A2) + IF(MONTH(A2)>=7,1,0) helps shift fiscal years that start in July (or any other month).

• Power Query for Advanced Grouping – Power Query allows automatic grouping by Fiscal Year and Quarter, making it ideal for dynamic reporting.

The Pivot Table Setup

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!

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.

How to Group by Fiscal Years & Quarters in Excel

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.

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)

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

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

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

 

Frequently Asked Questions

How do I group by fiscal years in a Pivot Table?
Select the date field in the Pivot Table, go to Analyze → Group Field, and choose Years and Quarters, adjusting the fiscal start month if needed.

How can I calculate a fiscal year in Excel using a formula?
Use =YEAR(A2) + IF(MONTH(A2)>=7,1,0) to assign the correct fiscal year for a July–June cycle. Adjust the month threshold as needed.

Can I group by fiscal quarters instead of calendar quarters?
Yes! Use a helper column with a formula like ="Q" & INT((MONTH(A2)-1)/3) + 1 and adjust for the fiscal year start.

What is the best way to handle fiscal grouping dynamically?
Power Query is the best option, as it allows automatic grouping by fiscal years and quarters when data is refreshed.

How do I change the default fiscal year start in Excel?
Excel doesn’t have a built-in setting for fiscal years, but you can manually adjust groupings in Pivot Tables or use formulas to define fiscal periods.