Pivot Table Group by Month

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.

You can download this Pivot Table Group by Month Excel Template here – Pivot Table Group by Month Excel Template

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.

Pivot table group by month Example 1

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.

Pivot table group by month Example 1-1

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.

Pivot table group by month Example 1-2

For the “Month” column, insert the below formula.

Pivot table group by month Example 1-3

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.

Pivot table group by month Example 1-4

Ok, now insert a pivot table by selecting the data.

Example 1-5 (Insert)

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.

Example 1-6 (fields)
Pivot table group by month Example 1-7

Change the report layout to the “Outline” form.

Pivot table group by month Example 1-8

Now our report looks like the below one.

Example 1-9 (Reports)

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.

  1. Insert the pivot table first like the below one.

    Pivot table group by month Example 1-1

  2. Right-click on any of the cells of the “Date” column and choose the “Group” option.

    Pivot table group by month Example 1-10

  3. When you click on the “Group” option, it will show us below the window.

    Pivot table group by month Example 1-11
    In this window, we can see it has picked automatic dates at starting at & ending at dates.

  4. 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

    Pivot table group by month Example 1-13

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.

Pivot table group by month Example 2-6

Click on “Ok,” and we will have a new pivot table like the below one.

Pivot table group by month Example 2-7

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.

Example 2-8 (month)

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.

Example 2-9 (Years Column)
Pivot table group by month Example 1-17

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.

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>