Excel offers amazing tools to group daily dates to months, quarters, years, or weeks. Episode 709 will show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the Mr. Excel netcast. I'm Bill Jelen talking about pivot tables this week.
Let's create a quick pivot table with dates going down the left-hand side.
So insert pivot table and ok. I'll choose the date field that goes down the left and the revenue field.
Well you know because we started out with transactional data ofcourse we get a report that shows daily dates going down the left-hand side.
It'd be nice if we could group that upto months or quarters or years very easy to do.
We go to the first date field.
I can either right click here, or now just up in the ribbon, I'll choose group selection and I get a great dialogue box that allows me to say maybe I want to show the information by months, quarters and years. We'll click ok and instantly excel is taking those daily dates and it has modified it to show us years, quarters and months and I want to look down here in the row labels area you see that there's actually three separate fields.
So we can take the years field now and move it across to column labels and we can now see a year-over-year comparison of 2007 versus 2008 very quickly using the grouping options within the pivot table.
Now one question that comes up. There's really no setting there for weeks. Let's take a look first of all, I want to ungroup and we go back to daily dates. If we group the selection, if we would choose only days from this drop-down notice the number of days button, this becomes enabled and we can group it up to seven days and now we have weekly periods.
This is also great if you have 13 months in your year.
The 28-day accounting periods you can group it up by 28 and create those accounting periods.
So some cool options there and besides the usual months quarters and years you can also basically create weeks or any other strange you know five-day periods or whatever you would ever need to do.
Well hey thanks for stopping by. Will see you next time for another netcast from MrExcel.
Let's create a quick pivot table with dates going down the left-hand side.
So insert pivot table and ok. I'll choose the date field that goes down the left and the revenue field.
Well you know because we started out with transactional data ofcourse we get a report that shows daily dates going down the left-hand side.
It'd be nice if we could group that upto months or quarters or years very easy to do.
We go to the first date field.
I can either right click here, or now just up in the ribbon, I'll choose group selection and I get a great dialogue box that allows me to say maybe I want to show the information by months, quarters and years. We'll click ok and instantly excel is taking those daily dates and it has modified it to show us years, quarters and months and I want to look down here in the row labels area you see that there's actually three separate fields.
So we can take the years field now and move it across to column labels and we can now see a year-over-year comparison of 2007 versus 2008 very quickly using the grouping options within the pivot table.
Now one question that comes up. There's really no setting there for weeks. Let's take a look first of all, I want to ungroup and we go back to daily dates. If we group the selection, if we would choose only days from this drop-down notice the number of days button, this becomes enabled and we can group it up to seven days and now we have weekly periods.
This is also great if you have 13 months in your year.
The 28-day accounting periods you can group it up by 28 and create those accounting periods.
So some cool options there and besides the usual months quarters and years you can also basically create weeks or any other strange you know five-day periods or whatever you would ever need to do.
Well hey thanks for stopping by. Will see you next time for another netcast from MrExcel.
