Excel pivot table only showing top 10 values

You might want to pull in a select number of values from your pivot table into your dashboard report i.e. the top 5, top 10, bottom 10, etc. To do this as a one-off is simple, but to do this dynamically (so the dashboard updates the top 10 as the data changes in the background) you’ll need to use one of the pivot table filtering options.

 

The below is a screenshot from a pivot table showing number of matches won in English top flight football since WW2.

 

PivotTableTopTen1

 

To filter the pivot table so it only ever returns the current top 10 teams, drop-down the Row Labels and choose Value Filters and select Top 10 to bring up this window:

 

PivotTableTopTen2

 

This is pre-set to the top 10 but allows you to switch between Top and Bottom, edit the number, or change from Items to Percent to return the top 10%. In this instance we keep it simple with the top 10.

 

PivotTableTopTen3

 

You might then sort these descending and setup formulas from your dashboard which lookup the pivot table, so the dashboard is always showing the current top 10. This is dynamic, so if 11th place Leeds United got some more Premier League wins under their belt (hard to imagine I know) and overtook 10th place West Ham United, the pivot table will automatically reflect that and Leeds would filter through to the dashboard page instead of West Ham.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *