Sabrina Ayon
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.
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.
- 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, 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.
- 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 and Month in Excel Pivot Table (with Easy Steps)
Similar Readings
- [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
- How to Group Dates by Filter in Excel (3 Easy Methods)
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.
- This will group the pivot table by months.
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!
