In episode 358, a great new trick from Adam in the UK to solve the problem from Episode 273. I had previously lamented that when you use the Top 10 AutoShow feature of a pivot table, there is no good way to show one line with all of the other customers. Adam's trick shows how to use the Group feature to solve the problem. Episode 358 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Way back in episode 273, I talked about how to use the Top 10 Auto Show feature on Pivot tables.
And I kind of lamented that there was no good way to have the Pivot table show you all of the other items, the items that weren't in the Top 10 filter.
Got a great note from Adam in the UK, who showed me this cool trick, for doing exactly what I wanted to do.
So we have a Pivot table here with Customer going down the left-hand side, and the first thing I want to do is get the Top 5 or 10 customers at the top of the list.
So I could double-click the Customer field and use the Sorting tools there, but instead, this is a one-time thing, I'm just going to sort this list descending based on revenue.
So the Top 5 customers I want to show are through rows 9(?), I'm going to select the other customers, right-click, choose Group and Show Detail, and say Group, and this creates a brand new field in our Pivot table called Customer2.
Now they call group one with the highly imaginative name of Group1.
So I'm going to click on that and type Other, so that way I have a different name, a name that I can choose, and then I'm going to take the Customer field and drag it off the report.
Now I have a list of the Top 5 customers and everything else is grouped as Other.
Very clever way, instead of using the Top 10 Auto Show feature, to basically just group all of the things that we don't want to see detail on into a single item.
So thanks to Adam for sending that along, great trick, I’ll make sure to show this all of my power Excel seminars, and we'll have to include it in a future book.
Hey, thanks for stopping by, we’ll see you tomorrow for another podcast from MrExcel!
Way back in episode 273, I talked about how to use the Top 10 Auto Show feature on Pivot tables.
And I kind of lamented that there was no good way to have the Pivot table show you all of the other items, the items that weren't in the Top 10 filter.
Got a great note from Adam in the UK, who showed me this cool trick, for doing exactly what I wanted to do.
So we have a Pivot table here with Customer going down the left-hand side, and the first thing I want to do is get the Top 5 or 10 customers at the top of the list.
So I could double-click the Customer field and use the Sorting tools there, but instead, this is a one-time thing, I'm just going to sort this list descending based on revenue.
So the Top 5 customers I want to show are through rows 9(?), I'm going to select the other customers, right-click, choose Group and Show Detail, and say Group, and this creates a brand new field in our Pivot table called Customer2.
Now they call group one with the highly imaginative name of Group1.
So I'm going to click on that and type Other, so that way I have a different name, a name that I can choose, and then I'm going to take the Customer field and drag it off the report.
Now I have a list of the Top 5 customers and everything else is grouped as Other.
Very clever way, instead of using the Top 10 Auto Show feature, to basically just group all of the things that we don't want to see detail on into a single item.
So thanks to Adam for sending that along, great trick, I’ll make sure to show this all of my power Excel seminars, and we'll have to include it in a future book.
Hey, thanks for stopping by, we’ll see you tomorrow for another podcast from MrExcel!
