How to Change Quarters in Excel Pivot Table

Struggling to make an Excel Pivot Table line up with your company’s fiscal quarters? You're not alone. While Pivot Tables are amazing for summarizing data, their standard date grouping defaults to the calendar year (January-March), which doesn’t work for everyone. This guide will walk you through exactly how to group your data by custom fiscal quarters and give you full control over your date-based reporting.

Why Don’t Pivot Tables Have a Simple Fiscal Quarter Setting?

Before jumping into the solution, it's helpful to understand the problem. When you drag a date field into a Pivot Table, Excel automatically groups it by years, quarters, and months. This is incredibly handy for a quick overview.

The issue is that this "Quarter" grouping is hard-coded to follow the standard calendar. Quarter 1 is always January, February, and March. There is no built-in toggle switch to say, "My fiscal year starts in July, so Q1 should be July, August, and September." This limitation forces us to be a little more clever and prepare our data beforehand - a simple process we'll cover next.

The A-Team Solution: Creating Helper Columns in Your Source Data

The most reliable and flexible way to handle custom fiscal periods is to add "helper columns" to your original data set before you create the Pivot Table. By creating new columns specifically for "Fiscal Quarter" and "Fiscal Year," you are telling Excel exactly how to categorize each transaction, taking all the guesswork out of the Pivot Table.

This method gives you complete control and ensures your reports are always accurate. It might seem like a bit of extra upfront work, but it will save you countless headaches down the line.

Step 1: Go to Your Source Data Table

This is a critical first step. All the work we’re about to do happens in the raw data table, not in the Pivot Table itself. Find the worksheet that contains the data feeding your Pivot Table. If your data isn't already in an official Excel Table, it’s a good practice to format it as one by selecting your data range and pressing Ctrl + T. This makes formulas and refreshing much easier to manage.

Step 2: Add a New “Fiscal Quarter” Column

Scroll to the right of your data and add a new column header. Let’s call it "Fiscal Quarter". This is where our formula will live.

Step 3: Write the Formula to Calculate Fiscal Quarters

Here’s where the magic happens. We'll use a formula to assign the correct fiscal quarter to each transaction based on its date. Let’s assume your company’s fiscal year starts in July, meaning:

July, Aug, Sep = Q1
Oct, Nov, Dec = Q2
Jan, Feb, Mar = Q3
Apr, May, Jun = Q4

In the first cell under your new "Fiscal Quarter" heading, enter the following formula. (Make sure to replace A2 with the cell containing the date in your first row of data.)

Press Enter, and if you're using an Excel Table, the formula should automatically fill down the entire column. If not, click the small square in the bottom-right corner of the cell and drag it down.

Breaking Down the Formula:

MONTH(A2): This function looks at the date in cell A2 and returns its month as a number (1 for January, 2 for February, and so on).
CHOOSE(...): The CHOOSE function acts like a selector. It looks at the number returned by MONTH(A2) and then picks a value from the list that follows.
1, 2, 3... within CHOOSE: The list of numbers 3, 3, 3, 4, 4, 4, 1, 1, 1, 2, 2, 2) corresponds to the fiscal quarters. Since January is the 1st month, CHOOSE picks the 1st item in our list (3), labeling it as Q3. Since July is the 7th month, it picks the 7th item (1), correctly labeling it as Q1.
"Q" & ...: This simply joins the letter "Q" to the number returned by the CHOOSE function, so your output looks like "Q1", "Q3", etc.

Step 4: Add a “Fiscal Year” Helper Column

Just creating a Fiscal Quarter isn't enough. For example, in a fiscal year starting in July, both December 2023 and January 2024 fall into the same fiscal year: FY2024. If we only use the calendar year, our reports will be split incorrectly.

Create another new column called "Fiscal Year". Here's the formula, again assuming a July start date:

Breaking Down the Formula:

YEAR(A2): This grabs the calendar year from the date in cell A2.
IF(MONTH(A2) >= 7, 1, 0): This is the logic part. The IF function checks if the month number is greater than or equal to 7 (July).
- If it is (e.g., August), the formula adds 1 to the calendar year. So August 2023 becomes Fiscal Year 2024.
- If it's not (e.g., February), it adds 0 staying in its correct Fiscal Year.

Feel free to prefix it with "FY" for clarity:

Step 5: Refresh Your Pivot Table

Now, head back to your Pivot Table. Click anywhere inside it, go to the PivotTable Analyze tab in the ribbon, and click Refresh. This makes your two new columns ("Fiscal Quarter" and "Fiscal Year") available in the PivotTable Fields list on the right.

If you don’t see the new fields after refreshing, your Pivot Table’s data source range might not have automatically updated. Click on Change Data Source (in the same ribbon tab) and verify that it includes your new helper columns.

Step 6: Build Your Pivot Table with the New Fields

You can now use your custom fields. In the PivotTable Fields list:

Drag the original date field out of the Rows or Columns area if it's there. Excel's automatic grouping will interfere with our custom fields.
Drag "Fiscal Year" into the Rows area.
Drag "Fiscal Quarter" underneath "Fiscal Year" in the same box.
Drag a value field (like Sales or Revenue) into the Values area.

You now have a perfectly organized report grouped by your company’s fiscal year and quarters!

Advanced Tip: Custom Sorting for Fiscal Quarters

After you set everything up, you might notice your quarters are sorted alphabetically (Q1, Q2, Q3, Q4 is fine, but this happens if you have Q1 and Q10). To fix this, you need to create a custom sorting list.

Go to File > Options > Advanced.
Scroll down to the "General" section and click Edit Custom Lists….
In the "List entries" box, type your quarters in the correct order, with each on a new line (e.g., Q1, press Enter, Q2, press Enter...).
Click Add, then OK twice to close the windows.

Now, refresh your Pivot Table one more time. It will automatically detect and sort your Fiscal Quarters using the logical order you just defined.

Final Thoughts

While Excel Pivot Tables don't offer a direct way to change quarter settings, creating formula-based helper columns in your source data gives you the power and precision you need. By adding Fiscal Quarter and Fiscal Year columns, you fully control how your data is summarized, ensuring your reports align perfectly with how your business operates.

We built Graphed to remove this kind of manual data preparation entirely. Instead of writing formulas, refreshing tables, and building Pivot Tables, we let you connect data directly from sources like Shopify, Google Analytics, or a Google Sheet. From there, you just ask for what you want in plain English, like "show campaign performance by fiscal quarter, starting in July." We handle all the data grouping and calculations automatically, serving up an interactive dashboard in seconds so you get the insights without the busywork.