MrExcel's Learn Excel #709 - Pivot Dates

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 Feb 18, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,536
Messages
6,114,207
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