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 a 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 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 at 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 the “ROWS” area and the “Sales” column to the “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 the 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 a quarterly summary, so we need to add another column and use a complex formula to arrive quarter number.
So, it looks tedious to tasks all of these. However, without adding any extra column, we can group the dates only in pivot tables itself.
Follow below steps to group pivot tables by dates.
- 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 below the window.
In this window, we can see it has picked automatic dates at starting at & ending at dates.
- Choose the “Group By” option as “Months” and click on “Ok” to group the dates by “Months.” And you will see the pivot table result as below
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 the 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 the “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 –