Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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! Full Feature Free Trial 60-day!
ad advanced combine rows group

arrow blue right bubbleGroup 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:
doc excel pivot table group by fiscal year 00
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:

arrow blue right bubbleRelated articles:

Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    fraktol · 3 months ago
    Hi, there appears to be an error in the formula above. For me, in Excel 365, =MONTH(A2)>="7" always evaluates to FALSE unless I remove the quotes.
English English
en Englishar العربيةzh-CN 简体中文zh-TW 繁體中文nl Nederlandsfr Françaisde Deutschit Italianoja 日本語ko 한국어pt Portuguêsru Русскийes Español