PivotTables: sort, top 3 and filters
Rank reps by sales, keep the top 3, and filter the whole report to two regions. Intermediate · 5 min · PivotTable, Sort, Top 10 filter, Report filter
A PivotTable lists its rows A to Z, but the question is usually "who sold the most?". You can sort by the values, keep only the top few, and add a filter that limits the whole report, like a slicer on the data.
The PivotTable at H1 totals Sales by Rep.
Sorting and the top few are in the ▾ drop-down on Row Labels, as in Excel (where they're Sort and Value Filters → Top 10). A field in Filters gets its own ▾ above the PivotTable.
Steps
Rank the reps: click the ▾ on Row Labels in
H1and set Sort to Largest value first.Keep the podium only: in the same settings, set Show to Top 3.
Now compare the North and South teams only: drag Region onto Filters. Then click the ▾ next to (All) at the top of the PivotTable, untick (All), and tick North and South.
Remember
- Sort rows by value to rank them, largest first.
- Top N keeps the leaders, and the Grand Total covers only those shown.
- A field in Filters limits the whole report to the items you tick, and is listed above the table.
Previous: PivotTables: rows, columns and percentages · Next: PivotTables: dates by month and quarter