How to Group by Month in Excel Pivot Table?

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.

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.

• 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.

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

• 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.

• 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.

• 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.

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.

• This will group the pivot table by months.

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

• How to Group by Month and Year in Excel Pivot Table
• [Fix] Cannot Group Dates in Pivot Table
• [Fixed] Excel Pivot Table Not Grouping Dates by Month

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