Pivot Table Group By Month How To Group Dates By Month

Pivot Table Group by Month

In any sector, data is captured daily, so when we need to analyze the data, we use PivotTables. So, it will also summarize all the dates and give them daily. But who will sit and see everyday transactions? Rather, they want to see the overall monthly total, which comprises all the dates in the month and gives the single total for each month so that we will have a maximum of 12 lines for each year. So, using Pivot Table in ExcelUsing Pivot Table In ExcelA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more, a grouping of dates into months is possible. This article will show you how to group dates by month.

Group Dates by Months

To demonstrate this example, we have prepared sample data you can download to practice with us.

This data is for two years, 2018 and 2019, daily. We need to summarize this data to get monthly sales values, so we need to extract month and year from the dates to analyze the sales on a monthly and yearly basis.

First, insert the PivotTable and apply the PivotTable, as shown below.

It has given us a daily summary report to arrive at month and year in two ways. First, we will see how to add months and years from the date column.

Insert two more columns and name them “Month” and “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.

Now, insert a PivotTable by selecting the data.

Now, drag and drop the “Year” and “Month” columns to the “ROWS” area and the “Sales” column to the “VALUES” area. We will have a PivotTable like the one below.

Change the report layout to the “Outline” form.

Now, our report looks like the one below.

Looks good. This technique is followed by users who do not know about the grouping technique in a PivotTable. We will see this now.

Group Dates in the Pivot Table

Inserting two extra columns to add month and year looks like an additional 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 at the quarter number.

So, it looks tedious to do all of these tasks. However, we can only group the dates in pivot tables without adding extra columns.

The problem here is we have two years of data. Since we have grouped by “Month,” it has grouped by months and did not consider years while choosing the grouping option to select both “Month” and “Year.”

In this window, it has picked automatic dates starting at and ending at dates.

Click on “OK.” We will have a new PivotTable like the one below.

It is exactly similar to the previous manual method we have followed.

So using PivotTable dates, only we can group the dates according to the months, years, and quarters.

We need to notice that 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 years as the second grouping criteria, we can see “Years” as the new field column, so instead of seeing the long PivotTable, drag and drop the “Years” field from the “ROWS” area to the “COLUMNS” area.

So now, we can easily look at and read the PivotTable report.

Things to Remember

We can group dates and time values in Excel.We must choose the kind of group we are doing.