How to Sort Pivot Table Months in Fiscal Year Order in Excel
Question details
The user needs to display pivot table month columns in a custom fiscal year order rather than the default calendar year order.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing periodic financial reports, such as cash-flow or profit-and-loss statements, where the company's fiscal year starts in a month other than January.
- Observed behavior
- Excel automatically sorts pivot table dates chronologically by the standard calendar year (January to December), and lacks a built-in feature to natively sort by fiscal year.
Verify that your source date column contains real Excel date values rather than text formatted to look like dates, as text cannot be properly grouped or sorted chronologically.
Use a Helper Column to Sort by Fiscal Month
Since Excel does not provide a built-in way to sort dates in a fiscal-year order, you can create a helper column containing a custom fiscal month number to dictate the sorting order.
By assigning a numerical value to each month based on your company's fiscal calendar (e.g., assigning 1 to October and 12 to September), you can force the PivotTable to arrange the months exactly how you need them.
Insert a new column next to your source data dates and name it 'Fiscal Month'.
Use a formula to convert the calendar date to a fiscal month number. For example, if your fiscal year starts in October, you can use a formula like =MOD(MONTH(A2)-10, 12)+1 (assuming A2 contains your date) and drag it down to fill the column.
Click anywhere inside your existing PivotTable, go to PivotTable Tools > Analyze, and click 'Refresh' to include the new column.
Drag the new 'Fiscal Month' field into the Columns or Rows area. Click the drop-down arrow on the Row/Column Labels filter, select 'More Sort Options', and sort the data in ascending order based on your new 'Fiscal Month' field.
Verify Setup Using the Group Selection Feature
If you are having trouble manipulating dates in your PivotTable, check if Excel recognizes your source data as valid dates using the built-in Grouping tool.
Create and Sort Pivot Tables Easily in WPS Office
WPS Spreadsheet offers powerful, intuitive PivotTable features that allow you to seamlessly group dates, add helper columns, and customize sorting orders for your financial reporting.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your financial data.
- 2. Insert a helper column: Add a new column next to your dates and use a basic formula to calculate the fiscal month order.
- 3. Create a PivotTable: Highlight your data range, navigate to the Insert tab, and select 'PivotTable'.
- 4. Apply custom sorting: Drag your fields into the Rows/Columns areas and use your new fiscal month helper column to sort the layout accurately.
Frequently Asked Questions
Can Excel automatically recognize my company's fiscal year?
No, Excel does not have a global setting to adjust PivotTables to a specific fiscal year. You must use formulas to create a helper column that defines the custom fiscal month numbering manually.
Why is the Grouping option greyed out for my PivotTable dates?
The grouping feature is disabled if your source data contains blank cells, text values formatted to look like dates, or errors within the date column. Ensure all cells in the column are formatted as numerical Date values.
How do I calculate the fiscal month if my fiscal year starts in July?
You can use the MOD formula to offset the months. Create a new column and use the formula =MOD(MONTH(A2)-7, 12)+1 (where A2 is the date cell). This will assign July as month 1, August as month 2, and so forth.
