Roll daily dates up to months and years using an Excel pivot table.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 23 - Month Rollups!
See, just wonder if maybe I'm allergic, it’ll be like the end of my career.
Hey chapter 23, we're talking about Pivot tables.
And about Pivot tables, we're talking about how to take daily dates and roll them up to months and years, it is so easy to do.
So here, I have 730 rows of daily data, Insert PivotTable, click OK, we'll choose Date and Sales, and of course I end up with daily dates down the left-hand side, that's not what I want.
So we want to go to the very first date field, on the PivotTable Tools, Options tab, under Group choose Group Field, and we're going to roll this up months, yes, always choose years.
If you chose just months, you would get January 1 year and January the next year rolled together into a single cell called January.
Click OK, and look at that, and we now have monthly data, 24 rows of monthly data, instead of hundreds of rows of daily data.
Now, the thing that's really cool here, see that years is a brand new field that wasn't in our original dataset, it's added by Excel.
Take years, and we can drag it over to column labels, and we now have 2009-2010, a nice year-over-year comparison, all made very easy, just a couple of clicks… (unclear) Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Excel In Depth chapter 23 - Month Rollups!
See, just wonder if maybe I'm allergic, it’ll be like the end of my career.
Hey chapter 23, we're talking about Pivot tables.
And about Pivot tables, we're talking about how to take daily dates and roll them up to months and years, it is so easy to do.
So here, I have 730 rows of daily data, Insert PivotTable, click OK, we'll choose Date and Sales, and of course I end up with daily dates down the left-hand side, that's not what I want.
So we want to go to the very first date field, on the PivotTable Tools, Options tab, under Group choose Group Field, and we're going to roll this up months, yes, always choose years.
If you chose just months, you would get January 1 year and January the next year rolled together into a single cell called January.
Click OK, and look at that, and we now have monthly data, 24 rows of monthly data, instead of hundreds of rows of daily data.
Now, the thing that's really cool here, see that years is a brand new field that wasn't in our original dataset, it's added by Excel.
Take years, and we can drag it over to column labels, and we now have 2009-2010, a nice year-over-year comparison, all made very easy, just a couple of clicks… (unclear) Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
