How to Group by Months in Excel Pivot Table?

One of the most important applications of Pivot tables is summarizing your data.

You can use Pivot tables to summarize data by a number of factors, including by month, day, quarter, and year.

In this tutorial, we will show you how to create a Pivot table to summarize your data grouped by month.

Table of Contents

Group by Month in Pivot Table in Excel

The following dataset contains data on food sales from different city branches of a fake company:

Let us create a pivot table to analyze the month-wise total sales made by this company. The process involves a series of steps, as shown below:

Make sure the dates are in the correct format
Create the Pivot table
Specify the Row, Column and Value fields
Group by Month and Year

Let us go over each of these steps one by one:

Step 1: Making Sure the Dates are in the Correct Format

The first step is making sure that your date column is in the right format.

This is quite an important step because your Pivot table will fail to group your data date-wise if the dates are not in the DATE format (by default it is mm/dd/yyyy in most cases).

To format the dates in our OrderDate column, we need to do the following:

Select the OrderDate column (which is column A in our dataset).
Right-click on the selected column and click on Format Cells.

Under the Category list, select the Date category.

Select the date category in the Format cells dialog box

From the right side, select the date format that you prefer, or leave it in the default format.
Click OK.

All your selected dates should now be formatted in the DATE format.

Also read: How to Group by Quarters in Excel Pivot Table?

Step 2: Creating the Pivot Table

Now that our dates are in the correct format, we can go ahead and create the Pivot table.

For this, follow the steps shown below:

Select any cell in your dataset.
From the Insert menu, select Pivot table.

This opens a dialog box as shown below:

Pivot Table from Table or Range dialog box

Make sure the correct range is specified in the ‘Table/range’ field.

If you want your Pivot table displayed in a new worksheet (which is recommended), then make sure the ‘New Worksheet’ radio button is checked. Otherwise you can check the ‘Existing Worksheet’ radio button.

Excel now creates a blank pivot table:

Your next task is to fill it with your required fields.

Also read: Cannot Group That Selection Error in Pivot Tables

Step 3: Specifying the Row, Column and Value Fields

To the right side of your Excel window, you should be able to see the PivotTable Fields sidebar. This lets you customize your Pivot table with your required fields.

The sidebar displays a list of fields from your original dataset with checkboxes next to each field title on top.

At the bottom, there are 4 boxes:

Filters
Rows
Columns
Values

Each item in the list of fields is also draggable, so you can simply drag the fields you want to be included in your Pivot table and drop them in the appropriate box, as follows:

We want our Pivot table to display the order dates (organized by month) and the Total sales for each month. So drag the OrderDate field from the list into the Rows box, as shown below:

Since we dragged a date value into the Rows box, Excel automatically grouped it in years and quarters. But we don’t want that. So uncheck the boxes next to Quarters and Years in the field list:

Next, drag the TotalPrice field from the list and drop it in the Values box. This will display the sum of total sales for each month in our Pivot table.

You should now see your month-wise total sales displayed in the Pivot table.

However, it is difficult to differentiate between sales for the year 2020 and those for the year 2021.

For example, the first row of the Pivot table displays the total sales for both Jan 2020 as well as Jan 2021.

That obviously does not make sense. So, we need to make sure we re-group the table first by year and then by month.

Step 4: Ungrouping the Date Column and Grouping by Month for each Year

At this point, the Pivot table is grouped by month only.

To group it by year and then by month, we will need to first ungroup it. So, follow the steps outlined below:

Select any cell in the date column of the Pivot table.
In the main menu you will notice two new tabs under PivotTable Tools – PivotTable Analyze and Design. Select the PivotTable Analyze tab.
From this tab, click on the Ungroup button (under Group).

This will ungroup the date column and display individual order dates.

To regroup this column by year and month, click on the Group Selection button.

From the dialog box that appears, select both Months and Years from the list under ‘By’, as shown below.

Click OK.

You should now see your Pivot table grouped by year and then by month, as shown below:

The Pivot table now makes more sense and it is easy to see month-wise total sales made by the company.

The year groups are collapsible, so you can simply click on the minus icon (‘-’) next to the year to collapse the group. This will show you the total sales for the whole year.

In this tutorial, we showed you, with a simple example, how to group by months in an Excel Pivot table. We hope you found it useful and easy to follow.

Other Excel tutorials you may also find helpful: