How To Group Date By Month, Year, half year or other specific dates In Pivot Table?
In Excel, if the data in a pivot table includes date, and have you tried to group the data by month, quarter or year? Now, this tutorial will tell you how to group date by month/year/quarter in pivot table in Excel.
There is a Group function in pivot table, you can apply it to group data as your need.
1. Select the data range you need, and click Insert > PivotTable. See screenshot:
2. In the Create PivotTable dialog box, check the option you need in the Choose where you want the PivotTable report to be placed section. See screenshot:
3. Click OK to create the pivot table. And in the PivotTable Field List pane, drag the field to the areas you need, and make sure the Date label is in Row Labels list. (In Excel 2016 and later versions, it will automatically group the Date into Years and Quarters), see screenshot:
4. Then go to the pivot table, right click anywhere under the Row Labels head, and select Group. See screenshot:
5. In the Grouping dialog, select the grouping criteria from the By list box. Here I select Month and Years. See screenshot:
6. Click OK to apply the function. Now you can see the data is grouped by month and year as following screenshot shown:
With the above method, you can group date by month, year, quarter quickly, but, sometimes, you may want to group date by specific date, such as fiscal year, half year, week number and so on. The normal Group function will not support to deal with it.
If you have Kutools for Excel, with its PivotTable Special Time Grouping feature, you can quickly group date by fiscal year, half year, week number, day of week, half an hour or specific minutes as you need.
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 specific date;
- (2.) From the Group By list box, choose the option that you need, in this case, I select Half year;
- (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 a cell of the 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 you will see the Pivot Table is created, and the dates are grouped by half year of each year. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agoOn dates columns I want no grouping watsoever. How to achieve that?
To post as a guest, your comment is unpublished.· 1 years agoso simple but so effective! Exactly what I wanted to do.
To post as a guest, your comment is unpublished.· 2 years agoI was searching on how to do that, thanks
To post as a guest, your comment is unpublished.· 2 years agoThank you so much!!! :D
To post as a guest, your comment is unpublished.· 2 years agoThank you very much...help me a lot!!!!
To post as a guest, your comment is unpublished.· 2 years ago@Jeroen The group dialog is multi select, make sure year and month are selected.
To post as a guest, your comment is unpublished.· 3 years agoI have date wise sales data from 01-01-2015 to 0-04-2018. How can i group it as
1st to 10th of every month
11th to 20th of every month
21st to 31st of every month
To post as a guest, your comment is unpublished.· 3 years agoThanks, great help. Now it groups values from January 2018 in the sale column as January 2017. Is there a way to have the year included in the grouping?
To post as a guest, your comment is unpublished.· 3 years agoReally Helpfull. Thank You
To post as a guest, your comment is unpublished.· 3 years agoThank you for the help!!
To post as a guest, your comment is unpublished.· 3 years agoVery useful instructions. Thank you!
To post as a guest, your comment is unpublished.· 3 years agoThese instructions were incredibly helpful! Thank you!