How to Group by Month in Pivot Table in Google Sheets

You can never understand the true power of spreadsheet tools such as Google Sheets and Excel until you master the use of Pivot tables.

These tables can bend and turn your data to squeeze out just the information you want to know. In this way, you get to understand your data in ways that simple formulae and filters would not let you.

Sometimes your data might need to be grouped by date, so you can make comparative studies or analyze trends over a period of time.

You can easily group your pivot table data by month, year, week, or even day. In this tutorial, we will show you how to group pivot table data by month.

Once you’ve mastered that, you can easily generalize this technique to group by year, week, or days.

How to Group By Month in Google Sheets Pivot Table

Let us say you have the following dataset:

From the above dataset, let us assume you want to create a pivot table that will show month-wise total sales. In order to do this, you need to move step by step. That means you will need to:

1. Ensure your date values are in a proper DATE format
2. Create a Pivot table that will show date-wise total sales
3. Group the Pivot table to display total sales by Month
4. Group the Pivot table to display total sales by Month for each year.

Let us go over these four steps one by one.

Ensuring that Your Date Values are in a Proper DATE Format

As you can see from the dataset, the dates are under the OrderDate column. This column displays the time period or duration during which the sales were made (or ordered). In order for the Pivot table group to work properly, it is important for the values in the OrderDate column to be in the DATE format, which is MM/DD/YYYY by default (in most cases).

To format your dates, follow the steps shown below:

1. Select the OrderDate column (column A in our Sheet).
2. Click on the Format menu from the menu ribbon.
3. Hover your mouse over the ‘Number’ option in the dropdown that appears.
4. This will display the Number sub-menu.
5. Now you can select the ‘Date’ option if you want your values to be formatted in the default date format.

Note: If you want some other date format, then you can hover your mouse over ‘More Formats’ after step 4, then click on ‘More date and time formats’. This will open the ‘Custom Date and time formats’ box, from where you can select the date format that you need.

Creating a Pivot Table to Show Date-wise Total Sales

Once your dates are in the correct format, creating the pivot table is really easy. Here are the steps that you need to follow in order to make a pivot table to show date-wise total sales from your dataset:

1. Click on the Data menu from the menu ribbon.
2. Select the Pivot Table option from the dropdown menu that appears.
3. You should now see a box asking if you want to insert your pivot table on the existing sheet or on a new sheet. Select the option that you prefer. For clarity, it is always better to create one in a new sheet.
4. Click on the Create button.
5. This should create your pivot table, either on the same sheet or a new sheet, depending on what you had opted for in step 3.
6. Your pivot table at this point should look like the screenshot shown below:
7. There should be a grid displaying ‘Rows’, ‘Columns’, and ‘Values’.
8. You can now start filling your pivot table with your required data. On the right side of the window, you should see a Pivot Table Editor. This will help you specify what should go into your Pivot table.
9. We now want our pivot table to have two columns (initially) – The Order Date and the Total sales on that date. So from the ‘Rows’ category, click ‘Add’.
10. From the dropdown list that appears, select OrderDate. This will add each unique Order Date to individual rows of your pivot table.
11. Next, we want to see Total sales for each Order Date. So from the ‘Values’ category, click ‘Add’.
12. From the dropdown list that appears, select Total. This will display the sum of all sales for each order date.

The Pivot table is already starting to make sense. But it would make more sense and be easier to read if the dates were grouped by month.

So, once you are done adding the rows, columns, and values you need, you can start to group the values by month.

Grouping the Pivot Table Values by Month

Google Sheets provides a really easy way to group pivot table values by dates. Here are the steps:

1. Right-click on any date in the OrderDate column.
2. From the context menu that appears, select or hover over ‘Create Pivot table date group’.
3. You should see a submenu with lots of options for grouping by date.
4. You will notice that there are a number of date options for you to group by. You can group by day, week, month, quarter, year and even a combination of these. To group by month, select the ‘Month’ option.
5. You also have the option to select ‘Year-month’. Doing this will group your data first by year and then by month for each year.

Here’s what your pivot table should look like now:

As you can see, this makes the pivot table a lot more useful and easy-to-grasp. However, you can go ahead and improve it even more, if you organize the columns by year and rows by month.

Grouping the Pivot table Values by Month for Each Year

A better way to make comparative and analytical studies of your data would be to organize the pivot table to display months in rows and years in columns as shown below:

For this, you will first need to sort the rows by ‘Month’ and not ‘Year-month’. Here are the steps you need to follow:

1. Right-click on any date in the OrderDate column.
2. From the context menu that appears, select or hover over ‘Create Pivot table date group’.
3. You should see a submenu with lots of options for grouping by date.
4. To group by month, select the ‘Month’ option.
5. Next, click on the ‘Add’ button in the ‘Columns’ category of the Pivot Table Editor.
6. From the dropdown list that appears, select OrderDate.
7. This should display every order date in columns.
8. Right-click on one of the dates.
9. From the context menu that appears, select or hover over ‘Create Pivot table date group’.
10. You should see a submenu with lots of options for grouping by date.
11. To group by year, select the ‘Year’ option.

Your Pivot table should now show each year in columns.

As you can see, it is now easier to see month-wise total sales and you can easily make comparisons between monthly sales in each year or observe trends in monthly sales over multiple years.

In this tutorial, we showed you a simple example of how to easily group data by months in a pivot table in Google Sheets. We hope it was helpful for you.