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

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
• Group data by half year in Pivot Table
• Group data by week number in Pivot Table
• Group data by day of week in Pivot Table
• Group data by half hour or specific minutes in Pivot Table

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:

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.

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:

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

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.

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:

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

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.

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:

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.

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:

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.

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:

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

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

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.