Learn how to filter a pivot table to only items from this week, last quarter, or next month. Episode 710 shows 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 MrExcel netcast.
I am Bill Jelen.
We've been talking about pivot tables this week.
Yesterday, I showed you how to take daily dates and roll them up two Weeks, Months, quarters and years.
I'll show you another trick.
And this is new in Excel 2007.
When we have a date field...
So, I choose insert Pivot table Click OK..
Let's put the dates down the left-hand side again and choose Revenue.
Well yesterday, I show you how to group those daily dates up two months.
If we go to the Date field and choose the drop down, there are now great date filters Let's take a look at these.
We can choose virtual filters like Next Week or This Week or Next Month, Next Quarter, Last Quarter.
Let's use this quarter, and we'll see that very quickly, we're able to limit this data down to just these records from the particular quarter.
And the great thing about this is that it is a virtual field.
So, if we would choose for example this week, every time that we open this pivot table and refresh, we're going to get records from just this week.
Those new Date Filters are brand new.
Now in order to use these, you have to be using Excel 2007.
You also have to have a data set that is not in compatibility mode.
You have to have this data set stored as an excel 2007 file.
If your data is still stored as a regular xls file, they're not going to let you add Date filters because they're afraid you might be sharing the the file with someone who's still using excel 2003, when this functionality did not exist.
Well hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
We've been talking about pivot tables this week.
Yesterday, I showed you how to take daily dates and roll them up two Weeks, Months, quarters and years.
I'll show you another trick.
And this is new in Excel 2007.
When we have a date field...
So, I choose insert Pivot table Click OK..
Let's put the dates down the left-hand side again and choose Revenue.
Well yesterday, I show you how to group those daily dates up two months.
If we go to the Date field and choose the drop down, there are now great date filters Let's take a look at these.
We can choose virtual filters like Next Week or This Week or Next Month, Next Quarter, Last Quarter.
Let's use this quarter, and we'll see that very quickly, we're able to limit this data down to just these records from the particular quarter.
And the great thing about this is that it is a virtual field.
So, if we would choose for example this week, every time that we open this pivot table and refresh, we're going to get records from just this week.
Those new Date Filters are brand new.
Now in order to use these, you have to be using Excel 2007.
You also have to have a data set that is not in compatibility mode.
You have to have this data set stored as an excel 2007 file.
If your data is still stored as a regular xls file, they're not going to let you add Date filters because they're afraid you might be sharing the the file with someone who's still using excel 2003, when this functionality did not exist.
Well hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
