Group Dates in a Google Sheets Pivot Table (An Easy Guide)

Dates can often be all over the place making it quite tough to summarize their corresponding values, even with pivot tables. However, to help with that we have something called “grouping” in pivot tables.

In this simple tutorial, we will show you how to group dates in a Google Sheets pivot table to better summarize data.

Let’s get started.

How to Group Dates in a Google Sheets Pivot Table

We will follow three very simple steps to reach the point where we can group dates in a pivot table of Google Sheets. Note that grouping is not limited to only dates in a pivot table.

Step 1: Check the validity of the source data. Especially that of the date values.

For the example, we will use the following dataset:

source dataset - group dates in google sheets pivot table

While it is simple to decipher, it is always a good idea to check the validity of the date. We will use the DATEVALUE function for this. Simply apply the formula in a separate cell.


using datevlaue function to check the validity of the dates

As you can see, all the dates return a numerical value, confirming that these dates are valid.

Step 2: Create a pivot table. We will use the current dataset to create the pivot table.

Select entire columns > Insert > Pivot Table > New Sheet

We will get the following pivot table:

pivot table created

Step 3: Add data to the pivot table. The objective is to present the total sales by date.

For the Rows condition, we use Date. And for Values, we use Sales data.

setting date as the primary row condition

As you can see, there is not much difference between the pivot table and the source dataset. This is why it is so important to further summarize this data by grouping.

At this point, we have several options to group by date values in the pivot table. Simply right-click over any date to bring up the pivot table group menu:

all date group options in the google sheets pivot table

However, we will focus on some of the most commonly used options with some iterations:

  1. Month
  2. Month and Year
  3. Quarter
  4. Week Number

Let’s see what they look like.

1. Group by Month in a Google Sheets Pivot Table

Continuing from where we left off. Right-click on any of the date values on the pivot table to bring up the grouping menu. This time, select the Month option.

selecting month to group by date in a google sheets pivot table

What we get is the sum of Sales for each month:

group by months in a google sheets pivot table

The blank row at the bottom can be ignored as it is accommodating all the blank cells in the source data set range.

One thing to note about this grouping of months is that years are not considered. So, whatever the year, as long as the data fall in a particular month, the data will be grouped accordingly.

2. Group by Year-Month

The next date group of pivot tables in Google Sheets is the Year-Month group:

selecting tear-month to group by dates in google sheets pivot table

This grouping includes the year that the month is in as well. This adds another layer to the data presented.

year-month grouping in a google sheets pivot table

Alternative: A Better Representation of Data by Grouping Year and Month Separately

Having the months separated by years is good. But what is better, and frankly, what should be done, is to keep the two sets of groups separate.

What we are looking for is a two-dimensional table with rows having the month group and the years as the columns. Let’s see how it’s done.

Step 1: Set the primary pivot table column to be grouped by months.

setting the primary grouping as months

Step 2: Add the Columns value as Date. This will set the two-dimensional layout that we are looking for.

adding date to columns

Step 3: This time, we will group the pivot table column as years.

Right-click on any date of the column > Create pivot date group > Year

selecting the year grouping

The result:

year and month group in a pivot table

3. Quarterly Grouping

Keeping the columns as years, let’s update the primary column to group the date as yearly quarters.

Right click over any date of the primary column > Create pivot date group > Quarter

selecting the quarter group option for dates in pivot table

The result:

year quarter grouping

4. Group by Week Number in a Google Sheets Pivot Table

Occasionally, a user may require the pivot table to group the dates as the week number of the year. This is a rare occurrence, so it is understandable why this grouping option is not available by default.

Thankfully, however, it is not impossible to achieve. We just need a little help, or more precisely, the use of a helper column.

Step 1: Back in the source dataset, create a column called ‘Week No.’. We will apply the WEEKNUM function here with reference to the Date column:


creating the helper column for week number

Note: This formula accommodates for black cells (with IF) and any future entries (with an open cell range and ARRAYFORMULA).

Step 2: Update the data range of the pivot table:

updating the range for the pivot table

If you are creating a new table altogether, just make sure to include the week number helper column.

Step 3: Add the ‘Week No.’ condition to Rows. Add ‘Sales’ to Values.

group by week number in google sheets pivot table

Final Words

Grouping dates in a Google Sheets pivot table is a great way to further summarize data. As we have shown, you can also use grouping to create two-dimensional date pivot tables. Of course, pivot tables do not limit groups to just dates.

Feel free to leave any queries or advice you might have for us 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