After limiting a pivot table report to the top five, the Grand Total does not show the total of everyone. This episode shows how to use a Data Filter in a pivot table. Recap:
The pivot table Top 10 Filter gives a total of the visible rows
Include Filtered Items in Totals is Greyed Out
Odd way to invoke the Data Filter from the magic cell
Data Filters are not allowed in pivot tables
Excel fails to grey out the Data Filter from the magic cell
Ask for the top 6 to get top 5 plus Grand Total
Useful for filtering by a specific pivot item
Excel 2013 or newer: Different Way to get the True Total
Send your data through the Data Model
Include Filtered Items in Totals will be available
Get Total with asterisk
I learned this trick 10+ years ago from Dan in Philadelphia
The pivot table Top 10 Filter gives a total of the visible rows
Include Filtered Items in Totals is Greyed Out
Odd way to invoke the Data Filter from the magic cell
Data Filters are not allowed in pivot tables
Excel fails to grey out the Data Filter from the magic cell
Ask for the top 6 to get top 5 plus Grand Total
Useful for filtering by a specific pivot item
Excel 2013 or newer: Different Way to get the True Total
Send your data through the Data Model
Include Filtered Items in Totals will be available
Get Total with asterisk
I learned this trick 10+ years ago from Dan in Philadelphia
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 1999 -- Pivot Table True Top Five I am podcasting this entire book.
There's a playlist, click the "i" on the top right hand corner to follow that playlist.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Alright, so we're going to create a Pivot Table and we want to show, not all the customers, but just the top five customers.
INSERT, Pivot Table.
Okay, I'll put Customer down the left-hand side and Revenue.
Alright so here's our whole list of customers noted as 6.7 million dollars.
Excel, makes it easy to do a top five.
Go into Row Labels, Value Filters, top 10.
Doesn't have to be top.
It can be top or bottom.
Doesn't have to be five.
It can be twenty, forty, it can be whatever.
Top eighty percent, give me enough records to get to three million dollars or four million dollars, but here we go.
Top five items.
Now remember 6.7 million dollars, click OK and my big problem here, is that, that grand total is not the 6.7 million.
When I give this to the VP of sales he's going to freak out, saying, wait a second, I know I did more than 3.3 million dollars.
Right, so we're going to undo, undo that and go back to the original data.
Now this next trick I learned during one of my Power Excel Seminars in Philadelphia.
A guy named Dan in row two, showed me this.
It was more than ten years ago that he showed me this trick, and first we have to talk about the Filters.
So normally, if you're going to use the regular Filter, this Filter here, you choose any one cell in your data set and click the Filter Icon, or some people choose the whole Data Set, CONTROL* and click the Filter Icon, but there's a third way.
A way that nobody cares about.
If you go to the very last Heading Cell, in my case, that's Cost in L1, and go one cell to the right.
I call this the magic cell, I have no idea why but for some unknown reason, from this cell, I can filter the adjacent Data Set.
Alright, it's like a weird way and no one cares about this.
Right, because there's two other really good ways to invoke a Filter, no one needs to know about the magic cell, but here's the thing, see inside of a Pivot Table, it's grayed out.
You're not allowed to use those Filters.
It's against the rules.
Now, if I come out here, I'm more than welcome to use the Filter but inside they grade out.
I don't know who the person is who grays this out, but they've never heard my little talk about the magic cell, because if I go to the very last Heading Cell and go one cell to the right, look at that, they forget to grey out the Filter and now I've just added the old Auto Filters to the Pivot Table.
So I come here, go to Number Filters, that's different than Value Filters.
It's still called Top Ten.
Slightly different, I'm going to ask for the top five, no the top six.
The top six because to this Filter the Grand Total is just another row, and the Grand Total is the largest item and then when asked for items 2 through 6, I get the top five items.
Alright, so there we are.
A cool Filter hack, that gives us the top five items and the true total of everybody.
Alright now, a couple things.
Don't forget about the magic cell.
Alright there's no way to turn this Filter off, unless you go back to the magic cell.
Alright so you need to remember the magic cell.
Also, if you change the underlying data and you refresh the Pivot Table, they're not going to refresh the Filter because as far as Microsoft knows, you're not allowed to have a Filter.
This is useful for other things.
Sometimes we have products going across the top.
Let's go here at a tabular form.
Not necessary, I just like to get real headings.
Gizmo, Widget, Gadgets, Doodads.
Alright and maybe you're the manager of Doodads and you need to see just the customers who had a particular value and Doodads.
So I go to the magic cell, turn on the Filter and then under Doodads I can ask for items that are greater than zero.
Click OK.
Alright, that type type of Filtering would not be possible on a regular Pivot Table, but it is possible using the magic cell.
Alright now let's undo the list.
Let's turn off this Filter and remove the Pivot Table, and if you're in Excel 2013 or new, I'm going to show you a completely legal way to get the correct total at the bottom.
Insert Pivot Table, down here at the bottom, starting in Excel 2013 this very innocuous box, doesn't sound very exciting, add this data to the Data Model.
That sends the data, behind the scenes, to the Power Pivot Engine.
Build the exact same report.
Customers down the left-hand side.
Revenue in the heart of the Pivot Table.
Then, go to the regular Filters, the Value Filters top 10.
Ask for the top five.
Notice again we have 6.7 million dollars after I do this, 3.3 million dollars but here's the difference.
When I go to the Design Tab, under Subtotals, this feature called Include Filtered Items in Totals, is no longer grayed out.
At a regular Pivot Table is not available.
We get a little asterisk there and it's the total of everything.
Alright, now of course that only works in Excel 2013 or newer.
Alright it's going to take six weeks for me to get this entire book out here on YouTube.
There's so many good tips here.
Tips that could start to save you time, right away.
Buy the entire book right now and you'll have access to all 40, It's actually a lot more than 40 tips.
Excel shortcut keys.
All kinds of great stuff in this book.
Alright, recap.
So when we do a Pivot Table top 10 Filter, it gives us the total but only the visible rows, not the stuff that it filtered out.
Yeah if we go to the second tab and look for Subtotals, Filtered Items and Totals, it's grayed out, but there is an odd way to invoke the Old Data Filter from the magic cell.
The very last heading cell, go one cell to the right, you can't use Filters and Pivot Tables, but if you go to the magic cell they forget to gray it out.
Now in the Number Filter, you ask for the top six to get the top five, plus the grand total.
Also useful for filtering to a specific Pivot Item: Doodads, anything that had greater than 0 in Doodads or top 5 Doodads.
Excel 2013 or newer, there's a different way to get the True Total.
Check that box for the Data Model and then include Filtered Items in Totals will be available.
You get the total with an asterisk.
And thanks to Dan in Philadelphia who showed me at one of my Power Excel Seminars, more than ten years ago, and gave me this great little trick.
A way for the Filter to sneak through the Club Pivot Table Wall.
They normally don't allow that Auto Filter.
There's a playlist, click the "i" on the top right hand corner to follow that playlist.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Alright, so we're going to create a Pivot Table and we want to show, not all the customers, but just the top five customers.
INSERT, Pivot Table.
Okay, I'll put Customer down the left-hand side and Revenue.
Alright so here's our whole list of customers noted as 6.7 million dollars.
Excel, makes it easy to do a top five.
Go into Row Labels, Value Filters, top 10.
Doesn't have to be top.
It can be top or bottom.
Doesn't have to be five.
It can be twenty, forty, it can be whatever.
Top eighty percent, give me enough records to get to three million dollars or four million dollars, but here we go.
Top five items.
Now remember 6.7 million dollars, click OK and my big problem here, is that, that grand total is not the 6.7 million.
When I give this to the VP of sales he's going to freak out, saying, wait a second, I know I did more than 3.3 million dollars.
Right, so we're going to undo, undo that and go back to the original data.
Now this next trick I learned during one of my Power Excel Seminars in Philadelphia.
A guy named Dan in row two, showed me this.
It was more than ten years ago that he showed me this trick, and first we have to talk about the Filters.
So normally, if you're going to use the regular Filter, this Filter here, you choose any one cell in your data set and click the Filter Icon, or some people choose the whole Data Set, CONTROL* and click the Filter Icon, but there's a third way.
A way that nobody cares about.
If you go to the very last Heading Cell, in my case, that's Cost in L1, and go one cell to the right.
I call this the magic cell, I have no idea why but for some unknown reason, from this cell, I can filter the adjacent Data Set.
Alright, it's like a weird way and no one cares about this.
Right, because there's two other really good ways to invoke a Filter, no one needs to know about the magic cell, but here's the thing, see inside of a Pivot Table, it's grayed out.
You're not allowed to use those Filters.
It's against the rules.
Now, if I come out here, I'm more than welcome to use the Filter but inside they grade out.
I don't know who the person is who grays this out, but they've never heard my little talk about the magic cell, because if I go to the very last Heading Cell and go one cell to the right, look at that, they forget to grey out the Filter and now I've just added the old Auto Filters to the Pivot Table.
So I come here, go to Number Filters, that's different than Value Filters.
It's still called Top Ten.
Slightly different, I'm going to ask for the top five, no the top six.
The top six because to this Filter the Grand Total is just another row, and the Grand Total is the largest item and then when asked for items 2 through 6, I get the top five items.
Alright, so there we are.
A cool Filter hack, that gives us the top five items and the true total of everybody.
Alright now, a couple things.
Don't forget about the magic cell.
Alright there's no way to turn this Filter off, unless you go back to the magic cell.
Alright so you need to remember the magic cell.
Also, if you change the underlying data and you refresh the Pivot Table, they're not going to refresh the Filter because as far as Microsoft knows, you're not allowed to have a Filter.
This is useful for other things.
Sometimes we have products going across the top.
Let's go here at a tabular form.
Not necessary, I just like to get real headings.
Gizmo, Widget, Gadgets, Doodads.
Alright and maybe you're the manager of Doodads and you need to see just the customers who had a particular value and Doodads.
So I go to the magic cell, turn on the Filter and then under Doodads I can ask for items that are greater than zero.
Click OK.
Alright, that type type of Filtering would not be possible on a regular Pivot Table, but it is possible using the magic cell.
Alright now let's undo the list.
Let's turn off this Filter and remove the Pivot Table, and if you're in Excel 2013 or new, I'm going to show you a completely legal way to get the correct total at the bottom.
Insert Pivot Table, down here at the bottom, starting in Excel 2013 this very innocuous box, doesn't sound very exciting, add this data to the Data Model.
That sends the data, behind the scenes, to the Power Pivot Engine.
Build the exact same report.
Customers down the left-hand side.
Revenue in the heart of the Pivot Table.
Then, go to the regular Filters, the Value Filters top 10.
Ask for the top five.
Notice again we have 6.7 million dollars after I do this, 3.3 million dollars but here's the difference.
When I go to the Design Tab, under Subtotals, this feature called Include Filtered Items in Totals, is no longer grayed out.
At a regular Pivot Table is not available.
We get a little asterisk there and it's the total of everything.
Alright, now of course that only works in Excel 2013 or newer.
Alright it's going to take six weeks for me to get this entire book out here on YouTube.
There's so many good tips here.
Tips that could start to save you time, right away.
Buy the entire book right now and you'll have access to all 40, It's actually a lot more than 40 tips.
Excel shortcut keys.
All kinds of great stuff in this book.
Alright, recap.
So when we do a Pivot Table top 10 Filter, it gives us the total but only the visible rows, not the stuff that it filtered out.
Yeah if we go to the second tab and look for Subtotals, Filtered Items and Totals, it's grayed out, but there is an odd way to invoke the Old Data Filter from the magic cell.
The very last heading cell, go one cell to the right, you can't use Filters and Pivot Tables, but if you go to the magic cell they forget to gray it out.
Now in the Number Filter, you ask for the top six to get the top five, plus the grand total.
Also useful for filtering to a specific Pivot Item: Doodads, anything that had greater than 0 in Doodads or top 5 Doodads.
Excel 2013 or newer, there's a different way to get the True Total.
Check that box for the Data Model and then include Filtered Items in Totals will be available.
You get the total with an asterisk.
And thanks to Dan in Philadelphia who showed me at one of my Power Excel Seminars, more than ten years ago, and gave me this great little trick.
A way for the Filter to sneak through the Club Pivot Table Wall.
They normally don't allow that Auto Filter.
