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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, 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!