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 Pivot Table

Let us say you have the following dataset:

Dataset to create Pivot Table

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.Format your data as dates

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.Click Data and then on Pivot Table
  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.Select New Sheet to get the Pivot Table
  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:Pivot Table inserted in Google Sheets
  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.Pivot Table Editor
  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’.Click on Rows in Pivot Table Editor
  10. From the dropdown list that appears, select OrderDate. This will add each unique Order Date to individual rows of your pivot table.Click on Order Date
  11. Next, we want to see Total sales for each Order Date. So from the ‘Values’ category, click ‘Add’.Click on Values
  12. From the dropdown list that appears, select Total. This will display the sum of all sales for each order date.Click on Total

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.Group by month in Pivot Table
  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.Group by Year and Month

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

Month wise grouped data in Pivot Table

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:

Data grouped by month and year in Google Sheets Pivot Table

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.Add Columns
  6. From the dropdown list that appears, select OrderDate.Click on Order Date
  7. This should display every order date in columns.Order date in columns in the Pivot Table
  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.Group by Year

Your Pivot table should now show each year in columns, as shown below:

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 group data by months in a pivot table. We hope it was helpful for you.

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.