Pivot Table - Filter on Top 20

[tdemarre]

In Excel 2016 I'm trying to filter on a pivot table to include only the top 20 accounts (coming from a very large cube containing 20,000 + account IDs).

I know it is very easy to filter on top 10, by simply clicking on Value Filters and choosing to include only Top 10. I was hoping there may be a similar way to include instead the top 20. Right now, I am just manually highlighting the top 20 rows and then right-clicking/including only those accounts. The problem is that any time the pivot refreshes I then have to unselect those and manually re-select the new top 20, which takes a lot of time given the size of the data set.

Any tips/advice would be greatly appreciated!

 

[liveinhope]

I'm using excel 2007 (and occasionally 2010 ) , never used 2016 but I'm sure you can still do this in 2016

"top 10" is just a STARTING POINT . select the "top 10" option . then in the box that comes up change the default "10" to "20" (you can show the top "any number you like" items .. ; you could show the "bottom"any number you like too)

In my pivot table I am showing the top 15

[TABLE="width: 178"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of score[/TD]
[/TR]
[TR]
[TD]A0003[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]A0004[/TD]
[TD="align: right"]183[/TD]
[/TR]
[TR]
[TD]a0005[/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD]A0007[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]A0009[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD="align: right"]271[/TD]
[/TR]
[TR]
[TD]A0011[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]A0013[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]A0016[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]A0017[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD="align: right"]248[/TD]
[/TR]
[TR]
[TD]A0022[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]A0024[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]A0025[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]A0027[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]2020[/TD]
[/TR]
</tbody>[/TABLE]

 

[tdemarre]

Oh wow....thank you! Yes, that is exactly what I was looking for. I can't believe I didn't realize that you can change the default 10 to any number.