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.
In the new window that appears, click the Months and Years options and then click OK:
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Group Data by Month in Excel
How to Calculate the Sum by Group in Excel
How to Count by Group in Excel