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.
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.
- 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.
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:
- 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.
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:
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:
- Excel Table vs. Excel Range – What’s the Difference?
- Find Last Monday of the Month Date in Excel (Easy Formula)
- How to Convert Month Name to Number in Excel?
- How to Autofill Dates in Excel (Autofill Months/Years)
- How to Group and Ungroup Worksheets in Excel
- How to Convert Date to Month and Year in Excel
- How to Delete a Pivot Table? 4 Different Ways!