Skip to main content

Quickly group data by fiscal year, half year, week number into Pivot Table in Excel

Author: Xiaoyang Last Modified: 2024-12-16

Kutools for Excel

Boosts Excel With 300+
Powerful Features

Normally, we can group date by months, years and quarters in Pivot Table quickly and easily. But, when you need to group data by fiscal year, half year, week number, etc, there is no built-in function for us to deal with these operations. However, Kutools for Excel provides a powerful feature – PivotTable Special Time Grouping, with this tool, you can quickly solve the following operations:


Group data by fiscal year in Pivot Table

If you need to group data by fiscal year in Pivot Table, please do as this:

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Pivot Table > PivotTable Special Time Grouping, see screenshot:

shot gruop time 1

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

(1.) First, select the data range that you want to create a Pivot Table which grouped by fiscal year;

(2.) From the Group By list box, choose Fiscal year option that you need;

(3.) Specify the start month that you want to set as fiscal year;

(4.) Then, select the date column you want to group by, if you group by date, here it will auto choose the Date column;

(5.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or a cell of current worksheet as you need.

shot gruop time 2

3. After finishing the settings, please click Ok button, and a new Fiscal year helper column is added at the right of the data range.

4. Then, the PivotTable is created with adding the Fiscal Year in the Rows section of PivotTable Fields pane. And please drag other fields to proper sections as you need. Now you will see the Pivot Table is created, and the dates are grouped by the fiscal year. See screenshot:

shot gruop time 3

At the same time, there is a column auto added beside the data range which is group date based on fiscal year.
shot gruop time 4


Group data by half year in Pivot Table

To group data based on half year in Pivot Table, please do with the following steps:

1. Click Kutools Plus > Pivot Table > PivotTable Special Time Grouping to enable this feature.

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

(1.) Firstly, select the data range that you want to create a Pivot Table which grouped by half year;

(2.) From the Group By list box, choose Half year option that you need;

(3.) Then, select the date column you want to group by, if you group by date, here it will auto choose the Date column;

(4.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or current worksheet as you need.

shot gruop time 5

3. After finishing the settings, please click Ok button, and a new Half year helper column is added at the right of the data range.

4. Then, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Half Year field to the Rows section, and then drag other fields to proper sections as you need. Now, a Pivot Table is created with the dates are grouped by the half year. See screenshot:

shot gruop time 6

At the same time, there is a column auto added beside the data range which is group date based on half year.
shot gruop time 7


Group data by week number in Pivot Table

If you want to group the data based on week number within a year in Pivot Table, this feature also can do you a favor:

1. Apply this feature by clicking Kutools Plus > Pivot Table > PivotTable Special Time Grouping.

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

(1.) Firstly, select the data range that you want to create a Pivot Table which grouped by week number;

(2.) From the Group By list box, choose Week number option that you need;

(3.) Then, select the date column you want to group by, if you group by date, here it will auto choose the Date column;

(4.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or current worksheet as you need.

shot gruop time 8

3. After finishing the settings, please click Ok button, and a new Week number helper column is added at the right of the data range.

4. Then, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Week number field to the Rows section, and then drag other fields to proper sections as you need. Now, a Pivot Table is created with the dates are grouped by the week number in a year. See screenshot:

shot gruop time 9


Group data by day of week in Pivot Table

To group the data by day of week in a Pivot Table, such as group by Monday, Tuesday…, please do as follows:

1. Apply this feature by clicking Kutools Plus > Pivot Table > PivotTable Special Time Grouping.

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

(1.) Fristly, select the data range that you want to create a Pivot Table which grouped by day of week;

(2.) From the Group By list box, choose Day of week option that you need;

(3.) Then, select the date column you want to group by, if you group by date, here it will auto choose the Date column;

(4.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or current worksheet as you need.

shot gruop time 10

3. After finishing the settings, please click Ok button, and a new Day of week helper column is added at the right of the data range.

4. Then, the PivotTable is created with adding the Half hour or Munites field in the Rows section of PivotTable Fields pane. And please drag other fields to proper sections as you need. Now, a Pivot Table is created with the dates are grouped by the day of week as below screenshot shown:

shot gruop time 11


Group data by half hour or specific minutes in Pivot Table

With this useful feature, you can also group the data by half hour or specific minutes in Pivot Table as you like.

1. Apply this feature by clicking Kutools Plus > Pivot Table > PivotTable Special Time Grouping.

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

(1.) Firstly, select the data range that you want to create a Pivot Table which grouped by half hour or specific minutes;

(2.) From the Group By list box, choose Half an hour or Minutes option that you need; (if you select Minutes, you should specify the interval minutes that you want to use into the Minutes box.)

(3.) Then, select the time column you want to group by;

(4.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or current worksheet as you need.

shot gruop time 13

3. Then, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Half hour or Minutes field to the Rows section, and then drag other fields to proper sections as you need. Now, a Pivot Table is created with the dates are grouped by the half hour or specific minutes as below screenshot shown:

shot gruop time 14

Note: You can check more than one criteria in the PivotTable Special Time Grouping dialog to group data.

shot gruop time 15 shot gruop time 16


Demo: Quickly group data by fiscal year, half year, week number into Pivot Table in Excel

 
Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Productivity Tools Recommended

Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs — no more cluttered windows. Know more...

Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010–2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...


Kutools for Excel

Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 – 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.

🌍 Supports 40+ interface languages
✅ Trusted by 500,000+ users and 80,000+ businesses worldwide
🚀 Compatible with all modern Excel versions
🎁 30-day full-featured trial — no registration, no limitations
Kutools for Excel RibbonKutools for Excel Ribbon