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

Row Labels	Sum of score
A0003	300
A0004	183
a0005	235
A0007	65
A0009	66
A001	271
A0011	79
A0013	87
A0016	87
A0017	92
A002	248
A0022	80
A0024	76
A0025	96
A0027	55
Grand Total	2020

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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.