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

or

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:

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Brings 300 Advanced Features to Excel, and Increase Your Productivity by 80%
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Kutools works with Office 2007-2019 and 365. It supports all languages, and very easy to install or deploy. Full features 60-day free trial.

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;

(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, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Fiscal Year field to the Rows section, and then 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:


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;

(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:


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;

(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;

(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, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Day of week 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 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. After finishing the settings, please click Ok button, and a new Half an hour or Minutes 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 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:

Pivot Table group data by half hour Pivot Table group data by specific minutes

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

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

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.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

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.

Be the first to comment.