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.
#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.
#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.
#4 Verify that pivot table is created properly. You can find that sales for all products are grouped by Year in this table.
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.
Step 2: In Grouping window, uncheck Years (which is selected by default), then select Months, then click OK.
Step 3: Verify that date is grouped by Months properly. Due to length limit, only part of table is displayed in below screenshot.
Step 4: Repeat step#2, this time you can check on both Year and Month to see the difference.
Step 5: Click OK on above screenshot. Verify that this time, Year is added properly.
You can click ‘-‘ before 2019 to shrink months. You can also click ‘+’ to spread all months.
Step 6: Repeat step#2 to enter Grouping again. This time select Quarters.
Step 7: Click OK. Verify that date is grouped by quarters properly.
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.
Click OK. Verify that only this period is grouped by months.
