What Is Pivot Table Group By Month In Google Sheets?
The Pivot table group by month in Google Sheets is a quick method to summarize the given time-based source data based on months. It helps us track the month-on-month pattern in our data.
Users can utilize the Pivot table group by month in Google Sheets option to monitor aspects such as the monthly sales figures and inventory levels at a firm.
For example, the source dataset shows the 2023 monthly sales figures of a firm.
The requirement is to summarize the sales data based on months in each quarter.
Then, we can use the Pivot table option, that is similar to Excel Pivot Table, to summarize the data based on months in each quarter, considering the meaning of the Pivot table group by month in Google Sheets explained previously.
The Pivot table group by month in Google Sheets method requires us to select the source dataset and then the Pivot table option from the Insert tab.
The Create pivot table window will appear. Here, we can choose the options to set the required destination location address where we want to show the pivot table. In this case, we shall set the target cell as E1 of the current sheet. Click Create.
The above action will open the Pivot table editor on the right of the work area. The pane shows various categories, such as Rows, Columns, and Values. It also shows the column headings we provided in the source dataset.
So, now, we can click the specific column heading in the pane. After that, we can drag and drop the column heading into the concerned category.
In this case, we update the quarters and months columns in the Rows category and the sales column in the Values category. Thus, we achieve the desired Pivot table, with the sales data grouped by months in each quarter using Pivot table group by month in Google Sheets option.
Table of contents
Key Takeaways
- The Pivot table group by month in Google Sheets is an option to epitomize a time-based dataset by month to analyze the monthly pattern in the source data.
- Users can use the option to group Pivot table data by month in Google Sheets to track a project’s monthly progress and evaluate metrics such as revenue and inventory level on a monthly basis.
- We can use the Pivot table option from the Insert tab to achieve a Pivot table, with its data grouped by month, in Google Sheets. However, another alternative is to use the QUERY function in Google Sheets.
How To Use Pivot Table Group By Month In Google Sheets?
The steps involved in the Pivot table group by month in Google Sheets method are the following:
- Open the worksheet containing the source dataset we aim to group by month in Google Sheets.
- Select the source dataset, and choose the Insert tab 🡪 Pivot table option. Next, the Create pivot table window will open, where we can select the destination location address to display the Pivot table. Next, click Create to open the Pivot table editor.
The pane has two sections. The first one shows the different sections or categories into which we can categorize the columns of the source dataset. The second section shows the column headings of the source dataset.
- Click, drag and drop the column holding the date values into the Rows category or section in the pane.
- Click, drag and drop the column with numeric data that we aim to group by month into the Values category in the pane.
- Right-click on a date value in the row labels. Next, select the Create pivot date group option’s Right arrow in the pop-up menu 🡪 The Month option to classify the pivoted data by month.
Examples
Let us see the examples to understand the meaning of Pivot table group by month in Google Sheets explained previously.
Example #1 – Group Dates By Months
We shall see an example of the Pivot table group by month and year in Google Sheets.
The source dataset shows the date-wise items sold by sales representatives at a firm. The data also includes the years and months based on the dates.
We can summarize the date-wise items sold data using Pivot Table group by month and year in Google Sheets as explained below:
Step 1: Select the source dataset range and then Insert 🡪 Pivot table.
The Create pivot table message box opens.
We need to show the Pivot table in the current sheet. So, we shall choose the Existing sheet radio button and update the target location address in the field below it.
Click Create to open the Pivot table editor.
Step 2: Click the Year column heading in the Pivot table editor. Next, drag and drop it in the Rows section in the pane.
Next, click the Month column heading in the Pivot table editor. After that, drag and drop it in the Rows section below the Year column.
Next, click the Items Sold column heading in the Pivot table editor. After that, drag and drop it in the Values section in the pane.
Close the editor pane to view the required Pivot table with the date-wise items sold data grouped by month and year.
Example #2 – Group Dates In The Pivot Table
The following dataset holds the date-wise inventory levels in various containers. Also, the dataset contains a column displaying the years based on the dates.
We can epitomize the date-wise inventory level data using Pivot table group by month in Google Sheets to understand the data trend better.
Step 1: Choose the source dataset range and then Insert – Pivot table.
Next, update the target cell information, where we want to show the Pivot table, in the Create pivot table message box that appears on selecting the Pivot table option.
Click Create to access the Pivot table editor.
Step 2: Click, drag and drop the specific column headings into the concerned sections or categories in the editor pane.
In this case, we shall have the Date column in the Rows section, the Year column in the Columns section, and the Inventory Level (Cartons) column in the Values section.
Step 3: Right-click a date value in the resulting Pivot table. Next, select the Create pivot date group right arrow – The Month option.
We will see the heading in column F in the Pivot table changing from Date to Date – Month, with column F showing months instead of the date values. Also, the Rows section shows the Date column heading updated as Date – Month.
All the inventory level values in the Pivot table, cited for the dates of the same month in each year, get added.
Thus, the Pivot table shows the inventory level values grouped by the months the specific dates fall into per year.
Example #3
The source dataset shows the date-wise stationery items’ total costs.
We shall group the dates by month for each stationery item.
Step 1: Select the source dataset range and then Insert – Pivot table.
The Create pivot table message box will open, where we update the target cell address to display the Pivot table.
Click Create to open the Pivot table editor.
Step 2: Click, drag and drop the required column headings into the concerned categories in the editor pane.
In this scenario, we shall have the Stationery Item column in the Rows section, the Date column in the Columns section, and the Total Cost ($) column in the Values section.
Close the editor pane to view the Pivot table.
Step 3: Right-click a date value in the Pivot table. Next, select the Create pivot date group right arrow – The Month option.
The dates in the Pivot table’s column headings now show as the corresponding months.
Furthermore, the total cost values get added based on the dates falling in the same month per stationery item.
Important Things To Note
- Before creating a Pivot table group by month in Google Sheets, ensure the source dataset is accurate. Otherwise, the values we get in the Pivot table after grouping by month will be incorrect.
- The date column in the source dataset should be in the correct and consistent date format. Otherwise, the dates may get grouped by month inaccurately in the resulting Pivot table in Google Sheets.
- Ensure the dates in the date column in the source dataset do not have additional non-date texts appended to them. Otherwise, we cannot group such dates by month in a Pivot table in Google Sheets.
Frequently Asked questions (FAQs)
We can group data by month in Google Sheets with the QUERY function instead of the Pivot table, as explained below with an illustration.
The source dataset contains the date-wise website visitors, with the customer base mentioned for the visitors.
The steps to categorize the customer base-wise website visitors by month using the QUERY() instead of the Pivot table option are as follows. Assume the target cell is E1.
Step 1: Select cell E1, enter the QUERY(), and press Enter.
=QUERY(HSTACK(B2:B11,ARRAYFORMULA(EOMONTH(A2:A11,0)),C2:C11),”SELECT Col2,SUM(Col3) GROUP BY Col2 PIVOT Col1 FORMAT Col2’MMM'”)
Next, format the compiled data according to the requirements to secure the following website visitors’ data, grouped by months. The website visitor counts get added based on the dates falling in the same month per customer base.
We can ungroup dates, grouped by month, in a Pivot table in Google Sheets using the following steps:
1. Right-click a month obtained by grouping the dates by month in the Pivot table.
2. Choose the Ungroup pivot items option from the pop-up menu.
We will see the dates instead of the months in the Pivot table, denoting the ungrouping.
The common mistakes when working with Pivot tables, with grouping by month, in Google Sheets are the following:
• The date column in the source dataset is not in the correct format. It leads to the dates getting grouped by month incorrectly.
• The dates cannot be grouped by month if the date values in the source dataset have additional non-date texts appended to them.
Download Template
This article must be helpful to understand the Pivot Table Group By Month In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Pivot Table Group By Month In Google Sheets. We learn how to use Pivot table group by month in Google Sheets with examples. You can learn more from the following articles –
Leave a Reply