How to Use Excel Pivot Table to Group Dates by Month and Year

Get FREE Advanced Excel Exercises with Solutions!

If you want to create Excel Pivot Table Group Dates by Month and Year, this article is for you. Here, we will walk you through 4 simple and easy methods to do the task effortlessly.

Download Practice Workbook


4 Methods to Use Excel Pivot Table to Group Dates by Month and Year

The following table has the Date, Product, and Sales columns. We want to create PivotTable to group dates by month and year for this table. To do so, we will go through 4 easy methods. All these methods are described step by step. In this article, we used Excel 365. You can use any available Excel version.

Excel Pivot Table Group Dates by Month and Year


1. Using Group Option from Context Menu to Group Dates by Month and Year in Pivot Table

In this method, first, we will insert a Pivot Table. After that, we will use the Group option from the Context Menu to PivotTable to group Dates by Month and Year. But before moving into that we will need to disable automatic grouping.

Step 1: Disabling Automatic Grouping 

If you have Excel 2016 or a higher version of Excel, you will notice that the dates are grouped automatically when the dates are kept in a row or column of the PivotTable. To avoid this automatic grouping, you have to follow the following steps.

  • First, go to the File tab of your Excel sheet.

Excel Pivot Table Group Dates by Month and Year

  • After that, select Options.

An Excel Options dialog box will appear.

  • Afterward, select Data >> mark or click on Disable automatic grouping of Date/Time columns in PivotTable.
  • Then, click OK.

This will disable the automatic grouping. Now, we will start our article.

Step 2: Grouping Dates by Month and Year

  • First, we will select the Entire Dataset >> go to the Insert tab.
  • After that, select PivotTable >> select From Table/Range.

A PivotTable from table or range dialog box will appear.

  • Then, we will select New Worksheet >> click OK.

Now, a new sheet will arrive for our Pivot Table. On the right side of the sheet, we will see PivotTable Fields.

  • After that, we will drag the Date and Sales to the rows and values area respectively.

Excel Pivot Table Group Dates by Month and Year

Now, we can see the PivotTable with the Row Labels, and Sum of Sales columns.

Next, we will group the dates by month and year in the Row Labels column.

  • Afterward, we will right-click on any of the cells of the Row Labels column.
  • Here, we right-clicked on cell B4.
  • Then, we will select the Group option from the Context Menu.

Excel Pivot Table Group Dates by Month and Year

A Grouping dialog box will appear.

  • After that, select the Month and Year.
  • Then, click OK.

Finally, you will see grouped Dates by Month and Year in Pivot Table.

Read More: How to Group by Week and Month in Excel Pivot Table (with Easy Steps)


2. Use of Group Selection Feature to Group Dates by Month and Year in Pivot Table

In this method, first, we will insert a PivotTable. After that, we will use the Group Selection feature from the PivotTable Analyze tab to pivot table group dates by Month and Year.

Steps:

  • First, we will insert a PivotTable by following the steps of Method-1.

Excel Pivot Table Group Dates by Month and Year

  • After that, we click on a date in the Row Labels column. Here, you can click on any of the cells of the Row Labels column.
  • Then, we go to the  PivotTable Analyze tab >> from Group >> select Group Selection.

A Grouping dialog box will appear.

  • After that, select the Month and Year.
  • Then, click OK.

Therefore, you will see grouped dates by Month and Year in Pivot Table.

Excel Pivot Table Group Dates by Month and Year

Read More: How to Group Pivot Table by Month in Excel (2 Methods)


3. Applying Group Field Command

In this method, first, we will insert a PivotTable. After that, we will use the Group Field command from the PivotTable Analyze tab to PivotTable group dates by Month and Year.

Steps:

  • First, we will insert a PivotTable by following the steps of Method-1.

  • After that, we click on a date in the Row Labels column. Here, you can click on any of the cells of the Row Labels column.
  • Then, we go to the  PivotTable Analyze tab >> from Group >> select Group Field.

A Grouping dialog box will appear.

  • After that, select the Month and Year.
  • Then, click OK.

As a result, you will see that all the Dates are Grouped by Month and Year in Excel Pivot Table.

Excel Pivot Table Group Dates by Month and Year

Read More: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions


4. Using PivotTable Fields to Group Dates By Month and Year

Here, we will add Year and Month columns to our dataset, therefore, there will be Year and Month fields in the PivotTable. As a result, when we will drag the Year and Month in the row area of the PivotTable, it will group the Date by Month and Year.

Steps:

  • First, we will type the following formula in cell E5.
=YEAR(B5)

Excel Pivot Table Group Dates by Month and Year

Formula Breakdown

  • YEAR(B5)The YEAR function returns a year corresponding to a Date.
  • YEAR(2-Jan-19) → becomes
    • Output: 2019
  • After that, press ENTER. Then, you will see the result in cell E5.
  • Afterward, we will drag down the formula with the Fill Handle tool.

  • Afterward, we will write the following formula in cell F5.
=TEXT(B5,"mmmm")

Formula Breakdown

  • TEXT(B5,”mmmm”) → The TEXT function changes the way a number appears by formatting it using format codes.
  • B5 is the value to format.
  • “mmmm” is the format code to apply.
  • TEXT(2-Jan-19,”mmmm”) becomes
    • Output: January
  • After that, press ENTER. Then, you will see the result in cell E5.
  • Afterward, we will drag down the formula with the Fill Handle tool.

Now, we will insert a PivotTable.

  • First, we will select the Entire Dataset >> go to the Insert tab.
  • After that, select PivotTable >> select From Table/Range.

Excel Pivot Table Group Dates by Month and Year

A PivotTable from table or range dialog box will appear.

  • Then, we will select New Worksheet >> click OK.

Now, a new sheet will arrive for our Pivot Table. On the right side of the sheet, we will see PivotTable Fields.

  • After that, we will drag the Year and Month to the rows areaand the Sales to the values area.

Excel Pivot Table Group Dates by Month and Year

Therefore, you will see that all the Dates are Grouped by Month and Year in Excel Pivot Table.

Excel Pivot Table Group Dates by Month and Year

Read More: How to Group by Year in Excel Pivot Table (3 Easy Methods)


Practice Section

In the practice section of your sheet, you can practice the explained methods.

Excel Pivot Table Group Dates by Month and Year


Conclusion

Here, we tried to show you 4 methods to use a pivot table to group dates by Month and Year in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo