How to Group Dates or A Period by Month/Quarter/Year in Pivot Table?

A pivot table is a table format that can dynamically arrange data and do category summarize strongly. It is commonly used in our daily life for creating summary report. It provides various combinations for row/column/values. Normally we can get the proper table format as we expect after setting group for these fields. As date or a period is an important parameter in summary report, in this article, we will introduce you how to group date (or a period of dates) by month/quarter/year in pivot table. This will help you to arrange your table group by different time settings flexible.

Initial Condition:

#1 Prepare a table with Product, Sales and Date.

Group Dates or A Period by MonthQuarterYear 1

#2 Then create pivot table for this table, then we can analysis data conveniently. Select range A1:C16, then click Insert->PivotTable, choose existing worksheet, locate pivot table in E1, then click OK.

Group Dates or A Period by MonthQuarterYear 2

#3 Verify that after clicking OK, PivotTable Fields settings pops up. Check on Product, Sales ($), Date in left control panel, and move Date to the top in Rows.

Group Dates or A Period by MonthQuarterYear 3

#4 Verify that pivot table is created properly. You can find that sales for all products are grouped by Year in this table.

Group Dates or A Period by MonthQuarterYear 4

Group Date or A Period by Month/Quarter/Year in Pivot Table


In above case, we know that date is grouped by Year by default. So, we just need to know how can we change Year to Month or others in pivot table.

Step 1: Select Year 2019 in pivot table, then right click to load menu, select Group.

Group Dates or A Period by MonthQuarterYear 5

Step 2: In Grouping window, uncheck Years (which is selected by default), then select Months, then click OK.

Group Dates or A Period by MonthQuarterYear 6

Step 3: Verify that date is grouped by Months properly. Due to length limit, only part of table is displayed in below screenshot.

Group Dates or A Period by MonthQuarterYear 7

Step 4: Repeat step#2, this time you can check on both Year and Month to see the difference.

Group Dates or A Period by MonthQuarterYear 8

Step 5: Click OK on above screenshot. Verify that this time, Year is added properly.

Group Dates or A Period by MonthQuarterYear 9

You can click ‘-‘ before 2019 to shrink months. You can also click ‘+’ to spread all months.

Group Dates or A Period by MonthQuarterYear 10

Step 6: Repeat step#2 to enter Grouping again. This time select Quarters.

Group Dates or A Period by MonthQuarterYear 11

Step 7: Click OK. Verify that date is grouped by quarters properly.

Group Dates or A Period by MonthQuarterYear 12

Comment:

If you just want to group a period of dates by months/quarters/year and others are not grouped, you can set start date and end date in Grouping. For example, enter 7/10/2019 as Starting at value, 10/10/2019 as Ending at value.

Group Dates or A Period by MonthQuarterYear 13

Click OK. Verify that only this period is grouped by months.

Group Dates or A Period by MonthQuarterYear 14