It helps you analyze data by getting different views by dates, weeks, months, quarters, and years. For example, if you have credit card data, you may want to group it in different ways (such as grouping by months or quarters or years). Similarly, if you have a call center data, then you may want to group it by minutes or hours. Watch Video – Grouping Dates in Pivot Tables (Grouping by Months/Years)
How to Group Dates in Pivot Tables in Excel
Suppose you have a dataset as shown below:
It has sales data by Date, Stores, and Regions (East, West, North, and South). The data spans across 300+ rows and 4 columns. Here is a simple pivot table summary created using this data:
This pivot table summarizes sales data by date, but it isn’t quite helpful as it shows all the 300+ dates. In such as case, it would be better to have the dates grouped by years, quarters, and/or months Download Data and follow along.
Grouping by Years in a Pivot Table
The dataset shown above have dates for two years (2014 and 2015). Here are the steps to group these dates by years:
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Years. While grouping dates, you can select more than one options. By default, Months option is already selected. You can select additional option along with Month. To deselect Month, simply click on it. It picks the ‘Starting at’ date and ‘Ending at’ date based on the source data. If you want, you can change these. Click OK
This would summarize the pivot table by years.
This summarization by years may be useful when you have more number of years. In this case, it would be better to have the quarterly or monthly data.
Grouping by Quarters in a Pivot Table
In the above dataset, it makes more sense to drill down to quarters or months to have a better understanding of the sales. Here is how you can group these by quarters:
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Quarters and deselect any other selected option(s). Click OK.
This would summarize the pivot table by quarters.
The issue with this pivot table is that it combines the Quarterly sales value for 2014 as well as 2015. Hence, for each quarter, the sales value is the sum of sales values in Quarter 1 in 2014 and 2015. In a real life scenario, you are most likely to analyze these quarters for each year separately. To do this:
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Quarters as well as Years. You can select more than one option by simply clicking on it. Click OK.
This would summarize the data by Years and then within years by Quarters. Something as shown below:
Note: I am using the tabular form layout in the above snapshot. When you group dates by more than one time-frame group, something interesting happens. If you look at the field list, you will notice a new field has automatically been added. In this case, it is Years. Note that this new field that has appeared is not a part of the data source. This field has been created in the Pivot Cache to quickly group and summarizes data. When you ungroup the data, this field will vanish. The benefit of having this new field is that now you can analyze the data with quarters in rows and years in columns, as shown below:
All you need to do is drop the Year field from Row area to Columns area.
Grouping by Months in a Pivot Table
Similar to the way we grouped the data by quarters, we can also do this by months. Again, it is advisable to use both Year and Month to group the data instead of only using months (unless you only have data for one or less than a year). Here are the steps to do this:
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Months as well as Years. You can select more than one option by simply clicking on it. Click OK.
This would group the date field and summarize the data as shown below:
Again, this would lead to a new field of Years getting added to the PivotTable fields. You can simply drag the years’ field to the columns area to get the years in columns and months is rows. You will get something as shown below:
Grouping by Weeks in a Pivot Table
While analyzing data such as store sales or website traffic, it makes sense to analyze it on a weekly basis. When working with dates in Pivot Tables, grouping dates by week is a bit different than grouping by months, quarters, or years. Here is how you can group dates by weeks:
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Days and deselect any other selected option(s). As soon as you do this, you would notice that the Number of Days option (at the bottom right) becomes available. There is no inbuilt option to group by weeks. You need to group by days and specify the number of days to be used while grouping. Note that for this to work, you need to select Days option only. In Number of days, enter 7 (or use the spin button to make the change). If you click OK at this point, your data would be grouped by weeks starting with January 4, 2014 – which is a Saturday. So the grouping would be from Saturday to Friday every week. To change this grouping and to begin the week from Monday, you need to change the start date (by default it picks the start date from the source data). In such a case, you can either start the date on December 30, 2013, or January 6, 2014 (both Mondays). Click OK.
This will group the dates by weeks as shown below:
Similarly, you can group dates by specifying any other number of days. For example, instead of weekly, you can group dates in a biweekly interval. Note:
When you group dates by using this method, you can not group it using any other option (such as months, quarters or years). Calculated field/item would not work when you group using Days.
Grouping by Seconds/Hours/Minutes in a Pivot Table
If you working with high volumes of data (such as call center data), you may want to group it by seconds or minutes or hours. You can use the same process to group the data by seconds, minutes, or hours. Suppose you have the call center data as shown below:
In the above data, the date is recorded along with the time. In this case, it may make sense for the call center manager to analyze how the call resolve numbers are changing per hour. Here is how to group the days by Hours:
Create a pivot table with Date in the Rows area and Resolved in the Values area. Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Hours. Click OK.
This will group the data by hours and you will get something as shown below:
You can see that the Row Labels here are 09, 10, and so on.. which are the hours in a day. 09 would mean 9 AM and 18 would be 6 PM. Using this pivot table, you can easily identify that most calls are resolved during 1-2 PM. Similarly, you can also group the dates on seconds and minutes.
How to Ungroup Dates in a Pivot Table in Excel
To ungroup dates in pivot tables:
Select any cell in the date cells in the pivot table. Go to PivotTable Tools –> Analyze –> Group –> Ungroup.
This would instantly ungroup any grouping that you have done. Download the Example File. Other Pivot Table Tutorials You May Like:
Creating a Pivot Table in Excel – A Step by Step Tutorial. Preparing Source Data For Pivot Table. How to Group Numbers in Pivot Table in Excel. How to Filter Data in a Pivot Table in Excel. How to Apply Conditional Formatting in a Pivot Table in Excel. How to Refresh Pivot Table in Excel. How to Add and Use an Excel Pivot Table Calculated Field. Using Slicers in Excel Pivot Table. How to Replace Blank Cells with Zeros in Excel Pivot Tables. Count Distinct Values in Pivot Table
Well I am using Excel 2010 standard. I do not see that ‘Analyze’ menu option on Ribbon. What can be the reason? Hi Sumit, I learned a new thing which is very helpful for me. I was doing this manually I also realized that when I checked button by button. Thanks for the clue. Sumit, thanks again for this good , useful tip !
