Pivot Table Group by Month

Article byWallstreetmojo Team
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Pivot Table Group By Month?

Pivot table group by month is used to group or summarize data based on dates. This helps users to calculate the total for each month as it automatically gives the total and saves users’ time as we don’t have to count daily transactions.

For example, consider the below table showing sales in different months in the year 2017 and 2018.

Pivot Table group by month Intro.jpg

Now, using pivot table group by dates option, we can calculate the grand total in 2017 and 2018 as shown in the below image.

Pivot Table group by month Intro - Output.jpg

As shown in the image, we can see the sum of sales month-wise and the grand total.

Key Takeaways

  • Pivot table group by month is an option used to find the total of data based on month in Excel.
  • This method helps users calculate the total for each month and also, summarizes the total sales. We can view the result in the pivot table.
  • The month can be calculated by using the formula =TEXT(value,format_text)
  • Similarly, year can be calculated using the formula =YEAR(serial_number)
  • Click on Design > Report Layout > Show in outline form to obtain the result in outline form.
You can download this Pivot Table Group by Month Excel Template here – Pivot Table Group by Month Excel Template

How To Use Pivot Table Group By Month?

We can group data by month using pivot table. This will help users calculate the total without any difficulty.

Let’s learn how to use pivot table group by month using the below examples.

Examples

Example #1 – Group Dates By Months

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

Pivot table group by month Example 1

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.

Pivot table group by month Example 1-1

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.

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

Next, insert a PivotTable by selecting the data.

Example 1-5 (Insert)

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.

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 one below.

Example 1-9 (Reports)

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

Example #2 – 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.

Follow the below steps to group PivotTables by dates.

  1. Insert the PivotTable first, like the one below.


    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, it has picked automatic dates starting at and 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 PivotTable result below.


    Pivot table group by month Example 1-13

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

Pivot table group by month Example 2-6

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

Pivot table group by month Example 2-7

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.

Example 2-8 (month)

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.

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

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

Important Things To Note

  • We can group dates and time values in Excel.
  • We must choose the kind of group we are doing.
  • To group, click on the pivot table and choose Group.

Frequently Asked Questions

1. What is 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.

2. Explain the use of pivot table group by month with an example.

Consider the below table showing sales in different months in the year 2015 and 2016.

Pivot Table group by month FAQ 2

Now, using pivot table group by dates option, we can calculate the grand total in 2015 and 2016 as shown in the below image.

FAQ 2 - Output
As shown in the image, we can see the sum of sales month-wise and the grand total.

3. How to find month and year in the data?

Consider the data has two columns – date and sales. To find the month and year, we can simply use the formulas – TEXT and YEAR.

The syntax of TEXT is =TEXT(value,format_text)
The syntax of YEAR is =YEAR(serial_number)

Recommended Articles

This article is a guide to PivotTable Group by Month. Here, we discuss how to group dates by months in a PivotTable with some examples. You may learn more about Excel from the following articles: