How to group by fiscal year in an Excel pivot table?
For example, the fiscal year begins in July in your company/organization, now you want to create a pivot table and group dates by the fiscal year within it, how could you deal with it? This solution may ease your work.
Recommended Excel Productivity Tools
Supposing there is a table as below screenshot shown. I will take this table as an example to create a pivot table and group dates by fiscal year within it easily in Excel.
1. Beside the source data, insert a new column, type Fiscal Year as column name, next type the formula =YEAR(A2)+(MONTH(A2)>="7") into the first cell of the new column, and finally drag the AutoFill Handle down to fill the whole column. See screenshot:
Note: In the formula =YEAR(A2)+(MONTH(A2)>="7"), A2 is the first date in the Date column, 7 means the fiscal year begins in July. You can change them as you need.
2. Select the new source data, and click Insert > PivotTable.
3. In the Create PivotTable dialog box, please select a destination range to place the pivot table, and click the OK button. See screenshot:
4. Now in the PivotTable Fields pane, please drag and drop the Fiscal Year field to the Rows section, and then drag other fields to proper sections as you need. See screenshot:
Now you will see the pivot table is created, and the dates are grouped by the fiscal year. See screenshot: