Grouping by quarters in an Excel Pivot Table can help you to summarize and analyze data more effectively.
Additionally, it can make it easier to identify significant patterns and trends in your data.
This tutorial shows how to group by quarters in Excel Pivot Table.
Grouping Dates By Quarters in Pivot Table in Excel
Let’s consider the following data range showing the sales data of a particular company.
We want to create an Excel Pivot Table based on the data range and then group dates by quarters in the Pivot Table.
We use the steps below.
Step #1: Convert the Normal Data Range to a Table
The data is in a normal range. Therefore, we can convert it to an Excel table before creating a Pivot Table.
Then, when we add new data to the table and save it, the Pivot Table will automatically update to include the latest data.
This automatic update eliminates the need to manually refresh the Pivot Table whenever we add new data to the source data.
If you're wondering what the difference is between an Excel table and an Excel range, click here to read an article I wrote about this difference.
To convert the normal range to a table, use the below steps:
- Select any cell in the dataset.
- Press Ctrl + T.
Excel makes an intelligent guess of the reference of the data range and displays the Create Table dialog box.
If the guess is incorrect, you can make the necessary adjustment to the reference on the ‘Where is the data for your table?‘ box on the dialog box.
- Click OK on the Create Table dialog box that appears.
The normal data range is converted to a table as depicted below:
Also read: How to Group by Months in Excel Pivot Table?
Step #2: Create the Pivot Table
To create a Pivot Table based on the table, we have just created, we use the following steps:
- Select any cell in the table.
- On the Insert tab, open the Pivot Table drop-down on the Tables group and choose the From Table/Range option.
- In the Pivot Table from table or range dialog box, select the New Worksheet option and click OK.
- On the Pivot Table Fields task pane on the right of the Excel window, drag the field with the date information from the fields list at the top of the task pane to the Rows box at the bottom of the task pane. Also, drag the field you want to group by quarters to the Values box and any other field or fields you want to summarize to the Columns box.
Notice that in this example, we have dragged the Order Date field to the Rows box because it is the field containing dates.
Step #3: Group by Quarters in the Pivot Table
We can group by quarters in the Pivot Table using the steps below:
- Open the Grouping dialog box by right-clicking any date on the Pivot Table and selecting Group on the shortcut menu.
Alternatively, on the Pivot Table Analyze contextual tab, click Group Selection on the Group group.
Excel displays the Grouping dialog box with the starting and ending dates already filled in based on the source data. However, you can change the dates if they are unsuitable for your work.
- On the Grouping dialog box, select Quarters and Years in addition to the already selected Months option.
- Click the Months option to deselect it so you remain with the selection of Quarters and Years.
Note: We include Years in the selection because we want the quarterly sales grouped by each year separately.
Otherwise, if we select Quarters only, the quarterly sales for all the years will be combined, as seen below, and we shall be unable to analyze the quarters for each year separately.
- Click OK on the Grouping dialog box.
The sales data is now grouped by quarters in the Pivot Table as shown below:
Note: Because we grouped the dates by more than one time-frame group, Excel added the Years field to the field list on the Pivot Table Fields task pane on the right of the Excel window.
The new field is not part of the source data. Instead, it has been created in the Pivot Cache to summarize the data, and it disappears when you ungroup the data.
One advantage of the new field is that we can drag it to the Columns box at the bottom of the Pivot Table Fields task pane,
Allowing us to summarize the data with quarters as row labels and Years as column labels, as shown below:
This tutorial explained how to group by quarters in Excel Pivot Table. We hope you found the tutorial helpful.
Other Excel articles you may also like: