Table of Contents
A pivot table is an effective tool for summarizing data, and grouping by month and year is a great way to organize data for analysis. To group by month and year in a pivot table, first select the field or fields that contain the date information, then right-click and choose Group. In the Grouping dialog box, select Months and Years from the Group By drop-down menu and click OK. The pivot table will now be grouped by month and year. This will allow you to easily compare data across multiple months or years.
Often you may want to group data by month and year in a pivot table in Excel.
Fortunately this is easy to do using the pivot table Group function.
The following example shows how to use this function in practice.
Example: Group by Month and Year in Pivot Table
Suppose we have the following dataset in Excel that shows the total sales made by some company during various days:
To group the sales by month and year, we can first create a pivot table.
Highlight the cells in the range A1:B12, then click the Insert tab along the top ribbon, then click PivotTable.
In the new window that appears, we’ll choose to insert the Pivot Table in cell D1 of the current worksheet:
In the window that appears on the right side of the screen, drag Date to Rows and Sales to Values:
The following pivot table will automatically be populated:
To group the data by month and year, right click on any of the dates in the pivot table.
Then click Group in the dropdown menu.
The sales in the pivot table will automatically be grouped by month and year:
We can now see the sum of sales, grouped by month and year.
For example:
- A total of 11 sales were made in January 2021.
- A total of 12 sales were made in February 2021.
And so on.