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

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How To Remove Special Characters in Excel
remove special character2

This post will show you how to remove special characters from text strings in Excel. And I am going to introduce two ways to remove special characters (@&)#%$) from a string in Excel. Removing Special Characters using Power Query If ...

How To Insert Comments in Protected Worksheet in Excel
insert commetns in protected workshee3

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel
convert text to upper cases1

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

Sidebar