Look Who it Was Random video

Excel - Pivot Table Top Five Report - Episode 358

478 views · Published 12 November 2009 · 2:08 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Pivot Table Top Five Report.

Welcome to another episode of MrExcel's Learn Excel series! In today's video, we're going to talk about a clever trick for displaying the top five or ten items in a Pivot table, while also grouping all the other items into a single category. This is a great solution for when you want to focus on the top performers, but also want to see the rest of the data in a more condensed format.

In episode 273, we discussed the Top 10 Auto Show feature on Pivot tables and how it can be limiting when it comes to showing all the other items that are not in the top 10. But thanks to a great tip from Adam in the UK, we now have a workaround for this issue. So let's dive in and see how it works!

First, we have a Pivot table with the Customer field on the rows section. To get the top 5 or 10 customers at the top of the list, we can simply sort the list in descending order based on revenue. Then, we can select all the other customers, right-click, and choose "Group and Show Detail" and then "Group". This will create a new field called Customer2, with the default name of "Group1". We can rename this to "Other" for a more descriptive name. Finally, we can remove the original Customer field from the report, and now we have a list of the top 5 customers and all the other items grouped under "Other".

This is a great alternative to using the Top 10 Auto Show feature, as it allows us to see all the data while still highlighting the top performers. Thank you, Adam, for sharing this clever trick with us. I'll be sure to include it in my future Excel seminars and books. And for more helpful Excel tips and tricks, make sure to subscribe to our channel and hit the notification bell so you don't miss any of our future videos. Thanks for watching, and we'll see you in the next episode of MrExcel's Learn Excel series!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to Pivot table Top 10 Auto Show feature
(00:22) Limitations of Top 10 Auto Show feature
(00:32) Trick for showing all other items in Pivot table
(00:42) Setting up Pivot table with Top 5 or 10 customers
(00:52) Grouping remaining customers as "Other"
(01:31) Alternative to Top 10 Auto Show feature
(01:41) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Creating a new field in a Pivot table
Excel book recommendations
Excel Pivot table techniques
Excel tips and tricks
Grouping non-top customers as Other in a Pivot table
Grouping Pivot table items
How to use the Top 10 Auto Show feature on Pivot tables
Power Excel seminars
Showing only Top 5 customers in a Pivot table
Sorting Pivot table by revenue
YouTube tutorial on Pivot table tricks


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152748/


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!

More from this channel