Pivot tables are powerful tools for summarizing and analyzing data—but only if you know how to use them properly.
One useful skill to know when using Pivot tables is how to group data together by date. Organizing your data by date information such as year, month, week, or even day can be useful when you’re looking at data.
In this tutorial, I will show you how to group a Pivot Table by Month in Google Sheets. I will also show grouping by other dates such as year or day.
Table of Contents
How to Group by Month in a Pivot Table
To show you how to group a Pivot table by month, I will be using a Pivot table created with this example data where I have the sales revenue for various days:
What I want to do with this data is have a Pivot table that will display total department revenue by month.
So first I will create a Pivot table and add my department and revenue information:
The “department” data in my example gets added to the “Rows” section of the Pivot table:
The “revenue” data in my example will be added to the “Values” section of the Pivot table:
Now what I am left with is a Pivot table that has the information that I want, Revenue and Department:
Now we are ready to add in the date data and group it by month. Here are the steps to do that:
1. First we need to add “Date” under the “Columns” section of the Pivot table
2. After you have added “Date” to the columns in the Pivot table, right-click on any Date in your pivot table and select “Create a pivot date group”
3. Then if you want to group the Pivot table by month, select “Month”
4. You should now see that your Pivot table is summarized by month
Grouping by Year, Quarter, or Day
You can also group your Pivot table by other date information such as year, quarter, day, and more.
The process for doing this is the same as doing it by month.
After you have added the dates to your Pivot table, right-click on a date, select “Create pivot date group” and from there you will find all the other options for grouping your Pivot table:
Closing Thoughts
Learning how to structure your Pivot table so that data is organized how you want to look at it is an important skill to develop for all spreadsheet users.
Grouping a Pivot table by month, year, day or other date information is relatively easy once you understand how it is done.
The key is going to be making sure that the date information in your raw data is formatted properly as a date. If you are getting an issue with grouping your data as I have shown in this guide, check your data and make sure your dates are formatted properly.
More Google Sheets Tutorials:
How to Use Calculated Fields in Pivot Tables
How to Use Slicers
How to Create a Pivot Chart