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

 

Related Posts

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute ...

Conditional formatting based on another column in Google Sheets or Excel

In Microsoft Excel Spreadsheet or google sheets, conditional formatting is used to highlight any cell based on a predefined condition and the value of those cells. In the previous article, we described how to format a specific column or cell ...

Conditional Formatting based on Another Cell in Google Sheets/Excel

In Microsoft Excel Spreadsheet or google sheets, when you want to format a specified cell or cell range based on the value of a different cell, for example, formatting the first row based on the value of a cell in ...

Compare Two Strings in Excel/Google Sheets

This article will talk about how to compare two given strings in a Microsoft Excel spreadsheet or Google Sheets. How to compare two strings in Excel by using VBA macros to see if they are the same, if they are ...

Compare effect of (non-annual) compounding periods on growth

This article will talk about how to calculate the trend or effect of future value changes for different compounding periods in a Microsoft Excel spreadsheet or Google Sheets via a formula. What is the period of compound interest? The compounding ...

Sidebar