Pivot Table Group by Month
In any sector, data is captured on a daily basis so when we need to analyze the data we use pivot tables, so it will also summarize all the dates and give every single day but who will sit and see everyday transactions rather they want to see what is the overall monthly total which comprises of all the dates in the month and gives the single total for each month in such way we will have maximum of 12 lines for each year. So, a grouping of dates into months is possible by using Pivot Table in Excel and in this article we will show you how to group dates by months.
Group Dates by Months
To demonstrate this example we have prepared a sample data that you can download the use the same to practice with us.
This data is of 2 years of 2018 & 2019 on a daily basis, ok we need to summarize this data to get monthly sales values, so we need to extract month & year from the dates to analyze the sales on a monthly and yearly basis.
First, insert the pivot table and apply the pivot table as shown below.
This has given us a daily summary report, so we can arrive month & year in two ways first we will see how to add month & year from the date column.
Insert two more columns and name it as “Month” & “Year” respectively.
For the “Month” column insert the below formula.
This text function takes the reference of the date column and applies the format as a short month name with code “MMM”.
Now apply the below formula to extract YEAR from the date column.
Ok, now insert a pivot table by selecting the data.
Now drag and drop Year & Month column to “ROWS” area and “Sales” column to “VALUES” area and we will have a pivot table like the below.
Change the report layout to the “Outline” form.
Now our report looks like the below one.
Looks good, the thing is this technique is followed by users who don’t know about grouping technique in a pivot table, we will see this now
Group Dates in the Pivot Table
Inserting two extra columns to add month & year looks extra task, now imagine the scenario where we need to see quarterly summary so we need to add another column and use a complex formula to arrive quarter number.
So, it looks tedious of tasks all of these. However, without adding any extra column, we can group the dates only in pivot tables itself.
Insert the pivot table first like the below one.
Right-click on any of the cells of the “Date” column and choose the “Group” option.
When you click on the “Group” option it will show us the below the window.
In this window we can see it has picked automatic dates at starting at & ending at dates, so choose the “Group By” option as “Months” and click on “Ok” to group the dates by “Months”.
Now you see the pivot table result.
The problem here is we have two years of data since we have grouped by “Months” it has grouped by months and did not take years into consideration, so while choosing grouping option to choose both “Month” & “Year” as well.
Click on “Ok” and we will have a new pivot table like the below one.
This is exactly similar to the previous manual method we have followed, isn’t it??
So using pivot table dates only we can group the dates according to the months, years, and quarters.
One thing we need to notice here is we had only “Date” as the “ROWS” area column but after grouping, we can see another field in the “ROWS” area section.
Because we have used the Year as the second grouping criteria we can see “Year” as the new field column, so instead of seeing the lengthy pivot table drag and drop the “Year” field from the “ROWS” area to “COLUMNS” area.
So now we can easily look and read the pivot table report.
Things to Remember
- We can group dates & time values in excel.
- We need to choose the kind of group we are doing.
This has been a guide to pivot table group by month. Here we discuss how to group dates by months in a pivot table with some examples. You may learn more about excel from the following articles –