Table of Contents
Grouping data by month in Excel involves using the GROUP BY clause of the GROUPING function to select a column of dates and group it into monthly intervals. This can be done by using either the DATE function or the EDATE function to create a new column of dates that are grouped into months, then using the GROUPING function to create the groups. An example of this process can be seen in the attached image, which shows a table of customer purchases that have been grouped by month.
Often you may want to group data by month in Excel.
Fortunately this is easy to do using the Group function within a pivot table.
The following step-by-step example shows how to use this function to group data by month in Excel.
Step 1: Create the Data
First, let’s create a dataset that shows the total sales made by some company on various days:
Step 2: Create a Pivot Table
Next, highlight the cells in the range A1:B12 and then click the Insert tab along the top ribbon and click Pivot table.
In the new window that appears, we’ll choose to insert the pivot table into cell D1 of the existing worksheet:
In the window that appears on the right side of the screen, drag the Date variable to the Rows group and the Sales variable to the Values group:
The following pivot table will appear:
Step 3: Group the Data by Month
To group the data by month, right click on any value in the Date column of the pivot table and click Group:
Once you click OK, the data in the pivot table will automatically be grouped by month:
The pivot table now shows the sum of the sales grouped by month.