How to Group by Quarters in Excel Pivot Table?

by

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.

data set to create a pivot table

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:

  1. Select any cell in the dataset.
  2. Press Ctrl + T.
creating Excel table using create table dialog box

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.

  1. Click OK on the Create Table dialog box that appears.
create table dialog box

The normal data range is converted to a table as depicted below:

data range converted to Excel table
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:

  1. Select any cell in the table.
  2. On the Insert tab, open the Pivot Table drop-down on the Tables group and choose the From Table/Range option.
click on the from table or range option
  1. In the Pivot Table from table or range dialog box, select the New Worksheet option and click OK.
insert pivot table in new worksheet
  1. 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.
pivot table fields drag into rows columns and values

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:

  1. Open the Grouping dialog box by right-clicking any date on the Pivot Table and selecting Group on the shortcut menu.
click on the group option

Alternatively, on the Pivot Table Analyze contextual tab, click Group Selection on the Group group.

click on the group selection option in ribbon

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.

  1. On the Grouping dialog box, select Quarters and Years in addition to the already selected Months option.
select months quarters and years in grouping dialog box
  1. Click the Months option to deselect it so you remain with the selection of Quarters and Years.
deselect the months option in grouping dialog box

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.  

pivot table dates grouped by quarters
  1. Click OK on the Grouping dialog box.

The sales data is now grouped by quarters in the Pivot Table as shown below:

pivot table dates grouped by quarters and years

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.

years field added to pivot table fields list

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,

add years to the columns area

Allowing us to summarize the data with quarters as row labels and Years as column labels, as shown below:

dates grouped by quarters and years in columns

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment