Course / PivotTables

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

  1. Rank the reps: click the ▾ on Row Labels in H1 and set Sort to Largest value first.

  2. Keep the podium only: in the same settings, set Show to Top 3.

  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

Previous: PivotTables: rows, columns and percentages · Next: PivotTables: dates by month and quarter