Create a Top 10 in a Pivot Table report where the grand total shows the total for all customers, not just the top 10.
Learn Excel 97-2007 from MrExcel. Get the book!
Learn Excel 97-2007 from MrExcel. Get the book!
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Hey, we discovered this one, this is a cool trick I discovered during a seminar in the past couple weeks.
You know that there's a Top 10 filter, so here I have a Pivot table, it’s 6.7 million $ worth the revenue, right.
And using the Top 10 filter we can come in here and say “Hey, I want to do Value Filters, Top 10, and do Top 10 items based on Sum of Revenue.” But the problem is, that was a 6.7 million & data set, and now it goes back to a 5.8 million, because it's only showing the total from the Top 10.
I'd like to have a Top 10 report that shows me the Top 10 customers, and the total from everybody.
OK so, check this out, I’m going to go to this cell right next to the heading, will go up to the Data tab, and from the Data tab choose Filter, and usually the AutoFilters are greyed out.
But, when you're in that one cell, that magic cell right next to the headings, you can actually filter a Pivot table, and then we'll use this and we'll say Number Filters, Top 10, Top 10 Items, click OK.
And then what we get is, we get the Top 9 items plus the grand total.
OK, so to pull this off, you actually have to go in Number Filters, Top 10, and ask for the Top 11! Click OK, and you now actually have a report of the Top 10 items and one grand total that shows the total of everything.
Now clearly we are cheating here, Microsoft isn't aware of the fact that you can AutoFilter a Pivot table.
Normally the filter is greyed out, outside of the Pivot table it’s there, inside the Pivot table it's greyed out.
But that one cell right there, cell C3 next to the headings, allows you to actually filter the Pivot table.
So, taking advantage of a bug?
Absolutely, yes we are, but a cool way to get a Top 10.
Now, just as a warning, if you refresh this Pivot table, it will not refresh the filter because, frankly, Microsoft didn't expect anyone to be filtering a Pivot table.
There you have it, cool trick.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Hey, we discovered this one, this is a cool trick I discovered during a seminar in the past couple weeks.
You know that there's a Top 10 filter, so here I have a Pivot table, it’s 6.7 million $ worth the revenue, right.
And using the Top 10 filter we can come in here and say “Hey, I want to do Value Filters, Top 10, and do Top 10 items based on Sum of Revenue.” But the problem is, that was a 6.7 million & data set, and now it goes back to a 5.8 million, because it's only showing the total from the Top 10.
I'd like to have a Top 10 report that shows me the Top 10 customers, and the total from everybody.
OK so, check this out, I’m going to go to this cell right next to the heading, will go up to the Data tab, and from the Data tab choose Filter, and usually the AutoFilters are greyed out.
But, when you're in that one cell, that magic cell right next to the headings, you can actually filter a Pivot table, and then we'll use this and we'll say Number Filters, Top 10, Top 10 Items, click OK.
And then what we get is, we get the Top 9 items plus the grand total.
OK, so to pull this off, you actually have to go in Number Filters, Top 10, and ask for the Top 11! Click OK, and you now actually have a report of the Top 10 items and one grand total that shows the total of everything.
Now clearly we are cheating here, Microsoft isn't aware of the fact that you can AutoFilter a Pivot table.
Normally the filter is greyed out, outside of the Pivot table it’s there, inside the Pivot table it's greyed out.
But that one cell right there, cell C3 next to the headings, allows you to actually filter the Pivot table.
So, taking advantage of a bug?
Absolutely, yes we are, but a cool way to get a Top 10.
Now, just as a warning, if you refresh this Pivot table, it will not refresh the filter because, frankly, Microsoft didn't expect anyone to be filtering a Pivot table.
There you have it, cool trick.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
