Note: When there are ties in top or bottom values, Excel will display all tied records. In the example shown, the pivot table is filtered on top 3, but displays 4 players, because Borg and Djokovic are tied for third place.
Data
The source data contains three fields: Year, Country, and Champion. This data is contained in an Excel Table starting in cell B4. Excel Tables are dynamic and will automatically expand and contract as values are added or removed. This allows the Pivot Table to always show the latest list of unique values (after refresh).
Fields
In the pivot table itself, only the Champion field is used, once as a Row field, and once as a Value field (renamed “Count”).
In the Values area, Champion is renamed “Count”. Because Champion is a text field, the value is summarized by Sum. In the Rows area, the Champion field has a value filter applied, to show only the top 3 players by count (i.e. the number of times each player appears in the list):
In addition, the Champions field is sorted by Count, largest to smallest:
Steps
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
