Roll daily dates up to months and years using an Excel pivot table.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel in Depth, Chapter 23.
Months Roll-up.
Just wonder if maybe, I'm allergic or like be the end of my career.
Hey chapter 23, we're talking about pivot tables and with pivot tables we're talking about, how to take daily dates and roll them up two months and years.
It is so easy to do.
So, here I have 730 rows of daily data.
Insert pivot table table, 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 Powerpivot tools.
Power pivot, on the pivot table 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, one 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 and 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 data set.
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.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
Excel in Depth, Chapter 23.
Months Roll-up.
Just wonder if maybe, I'm allergic or like be the end of my career.
Hey chapter 23, we're talking about pivot tables and with pivot tables we're talking about, how to take daily dates and roll them up two months and years.
It is so easy to do.
So, here I have 730 rows of daily data.
Insert pivot table table, 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 Powerpivot tools.
Power pivot, on the pivot table 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, one 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 and 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 data set.
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.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
