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.
Group by fiscal year in an Excel pivot table
Easily groups cells and calculate by values in another column in Excel
With Kutools for Excel’s Advanced Combine Rows feature, you can quick group all cells of one column based on values in another column, or calculate (sum, count, average, max, etc.) these cells by the values in another column at ease!
Group by fiscal year in an Excel pivot table
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:
Related articles:
Related articles:Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
