Excel in Depth - Days to Months: Podcast #1240

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 8, 2010.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top