How to Group by Month in Excel Pivot Table?

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we use pivot tables to summarize our data. Sometimes we need the data to be more specified. Excel makes it easy to do this by grouping the data. In this article, we will learn how to group a pivot table by month in Excel.


Introduction of the Dataset

The following dataset is about a shop, that mainly delivers cars. So the dataset is a list of the delivered cars of that shop. There are four columns in the dataset. Column B  contains the product model of the cars, Column C contains the product brand, Column D contains the price of the car model, and Column E contains the delivery date of the listed cars. Three bands of cars are listed in the following dataset: Hyundai, Suzuki, and Nissan. All the listed cars were delivered in January and February.

Group Pivot Table Manually by Month


How to Group by Month in Excel Pivot Table: 2 Methods

Grouping the pivot table helps to construct the data as per our desire. Grouping the pivot tables data by month is a great solution to structure the data properly in the following dataset. Let’s see the methods to group pivot tables by month in Excel. We will also have a look at how we can ungroup them.

1. Group Pivot Table Manually by Month

We can group the pivot table while creating the table. To do this, let’s take a view of the steps below.

STEPS:

  • Firstly, select the whole dataset and go to the Insert tab on the ribbon.
  • Next, from the Insert tab, go to the PivotTable drop-down menu and select From Table/ Range.

Group Pivot Table Manually

  • This will open the PivotTable from table or range dialog box. There we can see that the Table/Range is already selected as we select it before creating the pivot table.
  • After that, from Choose where you want the PivotTable to be placed option, we can choose the location where we want the pivot table. We want to place our table on a new worksheet. So we choose New Worksheet.
  • Then, click OK.

Group Pivot Table Manually by Month

  • Now, we can see that the pivot table is created in a new sheet.

Group Pivot Table Manually by Month

  • In the PivotTable Fields, we will customize the table as per our wish. We put the product brand and model in the Columns area and the price in the Values area. We will put the delivery date in the Rows area.

Group Pivot Table

  • Dragging the delivery date to the Rows area will create the month automatically. We can view the picture below there is another field now which is Months.

Group Pivot Table Manually by Month

  • So, the pivot table is grouped by January and February.

  • We can extend the data by clicking on the plus (+) sign.

  • After clicking the (+) sign, we can view the dates also. And following this way, we can group the pivot table by month easily.

https://www.exceldemy.com/excel-trim-spaces/

Read More: How to Group by Week in Excel Pivot Table


2. Automatically Group Pivot Table by Month

Assume that, we want to group the data by month in this pivot table. Let’s demonstrate the steps down.

STEPS:

  • In the beginning, select any cell in the Row Labels, where the delivery date is located.

  • After that, go to the PivotTable Analyze tab on the ribbon.
  • Then, select the Group Field in the group section.

  • Instead of doing that, we can just right-click on the mouse and select the Group.

  • At that moment, the Grouping dialog box will appear. In this window, we can see that the starting date and ending dates are automatically set.
  • Next, select the Months and click on the OK button.

Automatically Grouping Pivot Table by Month

  • This will group the pivot table by months.

Automatically Grouping Pivot Table by Month

Read More: How to Group by Year in Excel Pivot Table


How to Ungroup a Pivot Table in Excel?

We can ungroup the data if we need to view the whole data. To do this, just follow the steps below.

STEPS:

  • First, select the grouped data on the pivot table.
  • Then, go to the PivotTable Analyze tab > Ungroup.

By doing this, earlier group data will ungroup now.

  • Or, just right-click on the grouped data on the pivot table and select Ungroup.

Read More: How to Group by Week and Month in Excel Pivot Table


Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above methods are guidelines for grouping pivot tables by month in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.


Related Articles


<< Go Back to Group Dates in Pivot Table | Group Pivot Table | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo