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

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.


Download Practice Workbook

You can download the workbook and practice with them.


Introduction of the Dataset

The following dataset is about a shop, they mainly deliver 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 deliver in January and February.

Group Pivot Table Manually by Month


2 Methods to Group Pivot Table by Month

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 table 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, now we will customize the table as per our wish. We put the product brand and model in the Columns area and 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 and Month in Excel Pivot Table (with Easy Steps)


Similar Readings


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. And 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 Use Excel Pivot Table to Group Dates by Month and Year


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:

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

And by doing this, earlier group data will ungroup now.

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


Conclusion

The above methods are guidelines to group pivot table 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. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo