PivotdrillTop 10 filter
Click the Row Labels dropdown, choose Value Filters, Top 10, then set Top or Bottom, the number, Items, and the value field to rank by. The pivot keeps only those rows; the Grand Total then covers the shown rows only.
Syntax
Row Labels dropdown > Value Filters > Top 10 > Top | Bottom, N, Items | Percent | Sum, by field| Part | What it is |
|---|---|
Top / Bottom | Largest or smallest values |
N | How many items (or what percent, or what cumulative sum) to keep |
Items | Keep exactly N rows |
Percent | Keep the rows that together make up N percent of the total |
Sum | Keep the top rows until their values add up to N |
Examples
Rows: Product
Values: Sum of Units
Value Filters > Top 10 > Top 3 Items by Sum of UnitsResult: The three best-selling products and a Grand Total of just those three.
Rows: Customer
Values: Sum of Revenue
Top 10 > Top 80 Percent by Sum of RevenueResult: The customers that together produce 80 percent of revenue, a quick Pareto list.
Rows: Rep
Values: Sum of Revenue
Top 10 > Bottom 5 ItemsResult: The five weakest reps.
Add a sort largest to smallest afterwards if you want them in order; Top 10 does not sort.
Common mistakes
- The top 10 is not in order: the filter does not sort, so right-click a value and sort largest to smallest as well.
- Grand Total dropped: it now sums only the visible rows; use a second pivot or % of Grand Total before filtering if you need the full total.
- Ties give 11 rows: Excel keeps every item tied at the cutoff value.
Practice Top 10 filter
These drills check your answer on live data, free, no account needed.
- Products by units, smallest firstShow total Units by Product, sorted from the fewest units to the most.
- Best region firstShow total Revenue by Region, sorted so the region with the highest revenue is at the top.
Questions
How do I show only the top 5 in a pivot table?
Open the row dropdown, Value Filters, Top 10, change 10 to 5 and pick the value field. Click OK.
Can I filter the top 10 within each group?
Yes. Apply the Top 10 filter to the inner row field (for example Rep under Region). Each region then shows its own top reps.
Related
- Value filtersFilter pivot table rows by their totals: only regions with revenue over 10,000, counts below 5, or totals between two numbers. Steps and what gets tested.
- Sort a pivot tableSort a pivot table largest to smallest by a value, A to Z by label, or in your own order by dragging rows. More Sort Options and why a sort does not stick.
- Filter a pivot tableFour ways to filter a pivot table: the row label dropdown, the Filters area above the table, Value Filters on totals, and slicers. Examples and catches.