How to Group by Month in a Google Sheets Pivot Table (An Easy Guide)

featured image for group by month in a google sheets pivot table

Pivot tables are an amazing tool when it comes to summarizing large amounts of data in Google Sheets. We can further condense our findings by grouping the data according to certain values, like dates.

Thus, in this simple tutorial, we will look at how we can group a pivot table by month in Google Sheets.

Let’s get started.

How to Group by Month in a Google Sheets Pivot Table

Always Validate the Dates First

Dates can be written in many formats in a Google Sheets spreadsheet. However, as spreadsheets change hands and are shared among users, the date format the date is in may become invalid.

While rare, this usually happens due to the different regional settings of the users.

So, when doing calculations revolving around date values, it is crucial to check the validity of the dates in question.

example source dataset to group by month in google sheets pivot table

As you can see, the first column contains the date values. To validate these values, all you have to do is go to an empty column and use the DATEVALUE function referencing the cells that contain dates.


validating dates using the datevalue function

At first glance, the formula may seem a little complicated. But all we did was accommodate the entire Date column (A2:A) in the function and set a handler for any blank cells with IFERROR. You only have to apply this formula in a single cell.

As for the output, you can see we get a numerical value as a result. This indicates that the date is valid.

All that is left is to create a pivot table with this dataset.

Grouping By Month in the Pivot Table

With the pivot table created, we need to populate it with values.

created pivot table

Since we are grouping by dates, more specifically months, we will set the Rows condition from the Date column of the source dataset. We will also use Sales data as Values condition.

setting row and values for the pivot table

Note: You can use Filters to remove the blank cells from Sales.

To bring out the group menu, simply right-click over one of the date values and select the option “Create pivot table date group”. From here, you can select the type of grouping you want to do. For the sake of this article, we have chosen “Months”.

right click bring out the group by months menu in the pivot table

To summarize:

Right-click over a date value > Create pivot table date group > Month

group by month in a google sheets pivot table

The month grouping that we have just performed for this Google Sheets pivot table shows the sum of sales for specific months in all the years.

So why not also group by years to summarize the data further?

Include Year to the Table

If we want to further summarize by year, we must once again group the dates by years, but not in the same column as months.

Since we already have the months grouped in the primary column, we want to set the other columns as individual years.

Step 1: Set the Columns as Date values.

setting the column of the pivot table as date values in google sheets

Step 2: Group the columns as years. We will follow a similar procedure as we did with months, but this time, we choose Year.

Right-click over a date value > Create pivot table date group > Year

grouping pivot table column date values as years

The result:

group by month and year in a google sheets pivot table

Looks much better, doesn’t it?

The data now is much clearer to derive a conclusion from.

Quarterly Grouping in a Pivot Table in Google Sheets

Another popular form of grouping data in a pivot table is by quarters.

Many organizations, especially those involved in sales, like to analyze their data in quarters of the year. These are:

  • Q1: January, February, and March
  • Q2: April, May, and June
  • Q3: July, August, and September
  • Q4: October, November, and December

Thankfully, the option to group a Google Sheets pivot table by quarters is right there in the default group date options. Simply right-click on a date to bring it out:

Right-click over a date value > Create pivot table date group > Quarter

navigating to group dates by quarter in a pivot table

The result:

group by quarter and year in a google sheets pivot table

Final Words

With that, we conclude this simple guide on how to group a pivot table by month in Google Sheets. As long as the date values in the source dataset are valid, we can access the month group option simply by right-clicking over the date data in the pivot table.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply