How to Group a Pivot Table by Month in Google Sheets

In this guide, I have chosen to talk about how you can group a pivot table by month in Google Sheets by following easy and quick steps.

Welcome! I hope all of our previous articles from TeqTog were able to help you with using Google Sheets and, I have put together another such article just for you!

So, let’s get right to it!

Table of Contents

Pivot tables are incredibly helpful and it is important that you become skilled in using them; otherwise, you will be deprived of the many benefits that it can offer. Pivot tables can process large volumes of data and classify your information based on dates. This allows you to conduct analytical studies and find out any trends.

And that is exactly what I’ll try to teach you today.

I have a dataset which is about all the sales, which have taken place all throughout the year 2022.

Example Dataset to Group a Pivot Table by Month in Google Sheets

A Grouping Pivot Table by Month in Google Sheets: How to do it Step by Step

1. Dates are in the Correct Order

Before everything, you need to make sure that the dates in your data are formatted in “Date”; otherwise, assorting your pivot table will not work. Now, to convert the format of data into dates, you need to first select the column which has that data.

In the case of my example above, column A has all the dates. After selecting the column, just find the top menu in your spreadsheet and click on the option which says “Format”. Then, from the drop-down menu, find “Number” and hover your cursor over that. A second drop-down menu will appear and from there, you need to find and select “Date”. Now your dates are actually in the date format.

2. Making a Date-Wise Pivot Table

Now the fun begins, because you get to actually create the date-wise pivot table now. To begin with, find the “Insert” menu at the top of your spreadsheet. A drop-down menu will appear; and, from the options, go to “Pivot table” and select it.

When you select the “Pivot table”, a message box will pop up where you will be shown two options to select from: whether you would like to create the pivot table in the sheet you are already working on, or, on a new sheet. The choice is up to you; however, I have always found it convenient to create the pivot table in a new sheet.

How to Group a Pivot Table by Month in Google Sheets

After you have successfully created a pivot table by selecting “Create”, you will see a page that looks like the one shown below:

As you can see, there are grids which say “Rows”, “Values” and “Columns”. On the left side of the page, there is the “Pivot table editor”. The pivot table editor helps you to select the items you want to incorporate into your pivot table.

At least for now, we need two columns in our pivot table and they will be for the dates and the total sales on those dates.

To do this, go under the pivot table editor and find “Rows”. Besides it, click on the option “Add”.

Go Rows under the pivot table editor to Add

Google Sheets will now add every unique date to individual rows in my pivot table. Since I want to see each total sale for each date, I need to go the “Values” option and select the option “Total” – which stands for total sales.

Go to Values option and select the option Total

Start grouping your pivot table month-wise

When you have reached this point of adding your dates and the values which correspond with them, like I am here; you can start grouping your pivot table month-wise.

3. Grouping the Pivot Table Month-wise

Here are the steps to grouping pivot tables according to months.

First, right click anywhere on the column with the dates.
From the menu that drops down, click on the “Create pivot table date group”.
Here, a second drop-down menu will appear which will have all the options for the dates which you want to group your table by. You can choose any one of these options, or, you can also select a combination of them.

Since we have decided to group the dates by month, I will go to the “Month” option and click on that.

Pivot table based on grouping of example data according to months

As you can see for yourself, this is the pivot table that I have created based on grouping my data according to months. This organization of the data is much clearer to understand and convenient to look at and study.

In addition, this is not the final product yet. I can go ahead and further organize my data by incorporating in the years as well. By doing so, I will make the presentation of my data much more robust and easier for analysis and comparison.

I have always found it a smart idea to use the rows for months and the columns for years. So, to do this, I need to:

Grouping the Pivot Table According to Month and Year

Repeat the first two steps I have shown you for grouping the pivot table according to month. Just right click anywhere on the dates and hover your cursor on “Create pivot table date group” from the drop-down menu.
Select the “Month” option.
Now, go the “Pivot table editor” on the left side and find the option “Columns” and beside it, “Add”. Click on that.
From the drop-down menu for the option adding columns, I will select my “Date of Order”. Doing this will give the result as shown below:

Now, again right click on any one of the dates and from the menu, select “create pivot table date group”.
To assort by years, select the option which says “Year”.

Final results of Grouping the Pivot Table According to Month and Year

And, voila! Each of the dates is now shown as both months and years. Now, since the only year I had in my data 2022, there is only one year under column A – “2022”.

However, if there were multiple years, then those years would have been displayed exactly like this under the column which says “Date of Order – Year”.