Grouping by Date in a Pivot Table
by Jon Peltier
Wednesday, June 11th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
You can add flexibility to your data analysis by grouping variables. For example, you may have daily sales figures that fluctuate greatly in value, some days having no sales at all, some days with small values, and some with large values. A plot of this data is very noisy, and it’s hard to see any patterns.
In a pivot table, you can group dates by various measures ranging from seconds to years, to get a clearer view of the data. Plotting by month will smooth out the noise of a daily plot.
You can combine groupings, for example, to compare monthly sales by year.
These plots are regular charts which use pivot table data as their source. This technique will be covered in a (near) future post. The charts below are the corresponding pivot charts.
Microsoft hosts a number of decent Pivot Table examples. I will use one of them for this tutorial. Go to the Microsoft example 25 easy PivotTable reports, which has a link to Excel 2002 Sample: PivotTable Reports. From this page you can download a self-extracting executable named Reports.exe, which contains four workbooks. The top few records of the Source Data worksheet of the SampleSalespersonReports.xls workbook looks like this:
To create a pivot table, select one cell within this data range, and choose Pivot Table and Pivot Chart Report from the Data menu. To keep things uncomplicated, place the pivot table onto a new worksheet. Drag the Order Date field to the rows area and the Order Amount field to the Data area. The top few rows of the pivot table looks like this:
Click in this pivot table and click the chart wizard button. The resulting pivot chart looks like this when it is relocated to the worksheet:
To group the dates, right click anywhere in the date field range, choose Group and Show Detail, then choose Group.
To group by any time period, select that period in the dialog. To group by weeks, select Days, and change Number of Days to 7. To group by month, choose Months; note that this grouping will combine data for each month regardless of year. In this example, group by both Months and Years.
The Order Date column of the pivot table is split into a Years and a Months column.
With the new grouping of months and years, the pivot chart changes to plot months and years along the category axis.
Click on the Years field button, and drag it to the columns area, where the Total label is located. This splits the data into three yearly columns, each showing monthly totals for the respective year.
The pivot chart now shows one series of monthly sales for each year.
The dynamic nature of this pivot chart illustrates one of the benefits of pivot charts: the chart continues to display the data no matter how the table is pivoted. This makes pivot charts indispensable during the early stages of data exploration. The pivot chart’s relative inflexibility when it comes to formatting and handling of arbitrary (non-pivot-related) data, make pivot charts a liability during later stages of data presentation.
In an upcoming blog entry, I’ll describe how to create regular charts like those in the beginning of this article using data from a pivot table.
For more about pivot tables, check out these pages on my web site:
- Pivot Tables and Pivot Charts
Contributed by fellow Excel MVP Debra Dalgleish. Check out Debra’s Excel Tips page
– Introduction to Pivot Tables in Excel
– Working with Pivot Charts in Excel
– Pivot Table Programming
– Pivot Table and Pivot Chart Links - Pivot Tables, Pivot Charts, and Real Charts (TechTrax Article)
- Books on Excel Pivot Tables
– Excel Pivot Tables Recipe Book: A Problem-Solution Approach by Debra Dalgleish
– Beginning PivotTables in Excel 2007: From Novice to Professional by Debra Dalgleish
– Pivot Table Data Crunching by Bill Jelen and Michael Alexander
– Pivot Table Data Crunching for Microsoft Office Excel 2007 by Bill Jelen and Michael Alexander
Related Posts:
- Regular Charts from Pivot Tables
- Referencing Pivot Table Ranges in VBA
- Copy a Pivot Table and Pivot Chart and Link to New Data
- Explore Your Data With Pivot Tables
- Pivot Table Conditional Formatting with VBA
- Preliminary Data Exploration with Excel Pivot Tables
- Date Axis with Centered Years
- Using Pivot Table Data for a Chart with a Dual Category Axis
- Z Chart to Monitor Web Traffic
- Dynamic Chart using Pivot Table and Range Names
Posted: Wednesday, June 11th, 2008 under Pivot Tables.
Comments: 26
Comments
Comment from Consultant Ninja
Time: Wednesday, June 11, 2008, 7:39 am
Jon-
Interesting post. Is there a way to create primary & secondary level x-axis labels in a normal excel chart, not just a pivotchart? The Years and then the months above them looks very good, but I’ve never seen a solution that can produce that in normal charts.
Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 9:02 am
Ninja -
It’s all in the way you arrange the data:
Chart with a Dual Category Axis
The category labels take up two columns, and judicious use of blank cells in the first provides the arrangement of the levels. In fact, you can have more than two levels, at the expense of readability.
Comment from Consultant Ninja
Time: Wednesday, June 11, 2008, 3:58 pm
Works wonders. Thank you sir, I bow down to your excel wizardry.
Comment from Dan
Time: Thursday, June 12, 2008, 1:33 am
Hi Jon
This is great!
However, I was wondering if it would be possible to group dates by weeks as well?
I see that “Weekly” is not one of the options in the list.
Comment from Gary Waters
Time: Thursday, June 12, 2008, 8:01 pm
Dan,
In the grouping box, select Days only (hold down the control key and click on any other’s selected to deselect them). The ‘Number of Days’ box is now enabled, select 7. Now the dates will be grouped, using the Start at Date (like 6/1/2008 – 6/7/2008).
Comment from Ken Stern
Time: Tuesday, October 21, 2008, 3:16 pm
This is a great tip, thanks again. For anyone who happens to learn about it from this post, as I did, I’ll note that the data cannot have any blank cells, and that they must all be formatted as dates, for this feature to work. If not, you get the dreaded “Cannot group this selection.” error!
Thanks for the great blog and keep the tips coming!
Comment from Jon Peltier
Time: Tuesday, October 21, 2008, 3:24 pm
Ken -
I’m surprised I didn’t mention that. I spent an hour the first time I tried grouping dates, and it failed because my data source included a blank row.
Comment from Jon Peltier
Time: Monday, November 3, 2008, 1:22 pm
Mitch -
If any cells in the Date column contain non-dates, you will get this message, since Excel doesn’t know how to group such values with dates.
Comment from Mitch
Time: Monday, November 3, 2008, 1:00 pm
what if you receive the error message that you cannot group these dates in the pivot table??
What I am I doing wrong
Comment from Jay Killeen
Time: Sunday, November 9, 2008, 11:13 pm
Is there any way to group minutes in the same way that you have explained grouping days. Its driving me insane that it is giving the option to group days, and allows you to say how many days, but it does not do the same thing for every other time grouping option.
Any suggestions.
All I am trying to do is group loads of concrete delivered within 7:00am and 7:30am each day.
Comment from Jon Peltier
Time: Monday, November 10, 2008, 9:01 am
Hi Jay -
It looks like Excel’s grouping feature only gives you the ability to define a bin width if you are grouping by days.
The entry box below the list of time periods only says “Number of days”, and it is disabled if any time period other than days is selected:
Below is a set of times, randomly distributed between midnight and 4am. The first pivot table is merely a list (and I have chosen to sort by time). The second is grouped by hours and minutes. Only the hours and minutes that appear in the data are listed, unless you choose the field option to show items with no data, in which case you will display every minute of every hour in the table. There is no provision to group into 15 or 30-minute intervals. The last block uses the frequency array function to do what you ask. The first column defined the upper limit of each bin. The second column contains an array formula:
{=FREQUENCY(E5:E29,N5:N13)}
Don’t type the curly brackets. Select the entire range that will contain the output, type the formula into the formula bar, then hold Ctrl+Shift while pressing Enter. If done correctly, Excel rewards you with the curly brackets.
Comment from Richard Robinson
Time: Thursday, June 25, 2009, 6:04 am
The critical thing for me was not including the column headings in the pivot table selection! If you include them you can’t use the group by date function. You get an error message. After much hair-tearing I started again without the headings and it is fine.
Thanks v. much
Comment from Jon Peltier
Time: Thursday, June 25, 2009, 6:29 am
Richard – That’s right. Any non numeric values preclude grouping within that field.
Comment from Thom Mitchell
Time: Thursday, August 13, 2009, 10:53 pm
Most helpful for a Pivot Table newbie! Thanks, Jon!
Comment from Jon Peltier
Time: Friday, August 14, 2009, 12:34 am
Come on, Thom. No excuse to stay a newbie at such a useful and powerful feature.
Comment from Mark
Time: Wednesday, November 11, 2009, 8:28 am
Good tips, thanks Jon
Comment from sassybax
Time: Friday, November 27, 2009, 2:11 am
Thank you for this post. I was looking for a good tutorial on grouping by date but couldn’t find any until I hit this page.
Diane.
Comment from Pablo Santiago Sánchez
Time: Monday, February 8, 2010, 10:52 am
Does this work for Excel 2007? I’m kind of lost with the menus on this Office version…
Comment from Jon Peltier
Time: Monday, February 8, 2010, 3:05 pm
Hi Pablo -
When I use pivot tables in 2007 I still use the 2003 layout, which bypasses the enhancements in 2007 pivot tables and allows you to manipulate the pivlt table in place in the worksheet. I don’t know if you can right click on a field and choose group in the 2007 style, or if you have to find it in the task pane or ribbon someplace.
Comment from Pablo Santiago Sánchez
Time: Monday, February 8, 2010, 3:06 pm
How do I set Excel 2007 to work on the 2003 layout? :-(
Comment from Jon Peltier
Time: Monday, February 8, 2010, 4:57 pm
Pablo -
I think you can keep the 2007 layouts and the Group funcitonality will work.
To get the 2003 layout, which enables dragging of pivot field buttons, go to Pivot Table Tools > Options tab > Pivot table group (far left) > Options dropdown > Options, Display tag of dialog > Check box for Classic Pivot Table Layout.
Comment from Melissa
Time: Thursday, May 20, 2010, 8:39 am
I have a pivot table grouped in 7-day increments. When I sort it, however, it treats the dates as numerical values rather than dates, I think.
For example, it sorts the month of March as 3/1-3/7, 3/15-3/21, 3/22-3/28, 3/29-4/4, then sorts 3/8-3/14 last. The only reason I can think of is because it is sorting based on the numbers in order, rather than the date value. How do I fix this??
Comment from alison
Time: Thursday, May 20, 2010, 9:28 am
I have the same problem as Melissa, is there a way round this, as when you set to 7 days, it ignores the month and year, so group the 17th march with the 18th of april then you get the 24 march after this…
Comment from Jon Peltier
Time: Thursday, May 20, 2010, 12:10 pm
It’s sorting as text. 3/1 comes before 3/2, which is before 3/8. I usually use a dummy column with the first day of the week in it, and sort by that.
Comment from Srikanth
Time: Wednesday, July 7, 2010, 3:44 am
Was breaking my head days and hours to crack this..Your tips were really healpfull and worked…Thank you Jon.
Comment from gvolkov
Time: Friday, August 20, 2010, 10:42 am
Is this possible in Excel 2010? Tried everything but grouping menu is grayed out.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.