Normally, you can not use AutoFilter within a pivot table. Today, Dan from Philly shares an amazing hack that allows you to filter the results of the pivot table. Episode 793 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’m Bill Jelen.
Well, we just finished up the spring seminar season.
I love doing the seminars because, lots of times, someone in the audience will show me a brand new trick, and I have one of those today.
I was out near Philadelphia and Rich raised his hand, said, okay, well, you have that pivot table now but I need to filter.
I need to see all the records where the PROFIT is > $50,000.
Well, we can't do that in Excel 2003.
In fact, if you would go to DATA, FILTER, and try AUTOFILTER, it's grayed out.
Well, also in that seminar was Dan.
Dan raised his hand and he said, wait a second, I found a great work around for this.
He says what you do is you come here to the cell right next to the heading rows -- so, in this case, that would be cell G4 -- start in that cell, hold down the SHIFT key, and use the arrow keys to select all the headings.
Now, because we started outside of the pivot table, when you go to DATA, FILTER, AUTOFILTER is available.
This is amazing.
Turn on the AUTOFILTER and, now, I can create any kind of filter I want.
So, for example, if I come to the PROFIT heading, I would ask for a CUSTOM and say I want to see where it is GREATER THAN 50000, click OK, and, amazingly, I filtered the results of the pivot table.
Let’s go back.
DATA, FILTER SHOW ALL.
You know, there is a way to show the top 10 customers or top 5 customers.
Double click on CUSTOMER, click on ADVANCED, and then turn ON the TOP 10 AUTOSHOW, and ask for the TOP 5, click OK, but the problem with that it shows us the top 5 customers and then the total for only those 5.
I always like to see the total for everyone.
So, what we can do instead is kind of look at this and say, alright, basically, everyone larger than 600,000 is in the top 5.
So, we'll clear that TOP 10 AUTOSHOW, turn that OFF, and instead come here to the REVENUE dropdown, create a CUSTOM filter, and say, I want to see where the REVENUE IS GREATER THAN 600000, click OK, and there is our top 5 with a grand total of everybody because, of course, the grand total is > the 600,000.
An amazing trick.
I want to send a thanks out to Dan in Philly for showing us how we can create filters within a pivot table.
The trick, again, is just to start right out here, right outside of the pivot table.
Now, by the way, when you would want to turn this off normally, if you're inside the pivot table, you can't go and uncheck AUTOFILTER but you have to come to that extra cell and use DATA, FILTER, AUTOFILTER to turn the AUTOFILTER off -- very, very cool trick.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, we just finished up the spring seminar season.
I love doing the seminars because, lots of times, someone in the audience will show me a brand new trick, and I have one of those today.
I was out near Philadelphia and Rich raised his hand, said, okay, well, you have that pivot table now but I need to filter.
I need to see all the records where the PROFIT is > $50,000.
Well, we can't do that in Excel 2003.
In fact, if you would go to DATA, FILTER, and try AUTOFILTER, it's grayed out.
Well, also in that seminar was Dan.
Dan raised his hand and he said, wait a second, I found a great work around for this.
He says what you do is you come here to the cell right next to the heading rows -- so, in this case, that would be cell G4 -- start in that cell, hold down the SHIFT key, and use the arrow keys to select all the headings.
Now, because we started outside of the pivot table, when you go to DATA, FILTER, AUTOFILTER is available.
This is amazing.
Turn on the AUTOFILTER and, now, I can create any kind of filter I want.
So, for example, if I come to the PROFIT heading, I would ask for a CUSTOM and say I want to see where it is GREATER THAN 50000, click OK, and, amazingly, I filtered the results of the pivot table.
Let’s go back.
DATA, FILTER SHOW ALL.
You know, there is a way to show the top 10 customers or top 5 customers.
Double click on CUSTOMER, click on ADVANCED, and then turn ON the TOP 10 AUTOSHOW, and ask for the TOP 5, click OK, but the problem with that it shows us the top 5 customers and then the total for only those 5.
I always like to see the total for everyone.
So, what we can do instead is kind of look at this and say, alright, basically, everyone larger than 600,000 is in the top 5.
So, we'll clear that TOP 10 AUTOSHOW, turn that OFF, and instead come here to the REVENUE dropdown, create a CUSTOM filter, and say, I want to see where the REVENUE IS GREATER THAN 600000, click OK, and there is our top 5 with a grand total of everybody because, of course, the grand total is > the 600,000.
An amazing trick.
I want to send a thanks out to Dan in Philly for showing us how we can create filters within a pivot table.
The trick, again, is just to start right out here, right outside of the pivot table.
Now, by the way, when you would want to turn this off normally, if you're inside the pivot table, you can't go and uncheck AUTOFILTER but you have to come to that extra cell and use DATA, FILTER, AUTOFILTER to turn the AUTOFILTER off -- very, very cool trick.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
