Excel Top 10 filter shows >10 items

Question

_{Wednesday, November 21, 2012 1:52 PM}

Hi,

I have a pivot table in Excel which is filtered to top 10 items. However if item 10 and item 11 are the same, it includes both, so ultimately the table contains 11 items. Whilst this is mathematically correct, I only want to see 10 items, as I want to avoid pivot table overlaps which can become unavoidable if the top 10 includes 20 items!

I have considered hiding the actual pivot table and then creating a table of links which is only 10 lines long, but I would prefer to avoid such a workaround if at all possible.

Thanks

All replies (3)

_{Wednesday, November 21, 2012 2:32 PM ✅Answered}

You need to decide how to break ties, with a column of formulas, and include the tie-breaking column  in your pivot table source, and then use that as your top 10.

_{Tuesday, November 27, 2012 3:03 PM}

Hi Bernie,

Thanks..

In the end I just did some lookups on the first 10 entries of a pivot without breaking ties.

Not ideal but I didn't have time to work on an extra formula to break ties.

Thanks

_{Tuesday, November 27, 2012 3:26 PM}

Tie-breaking is easy.  For example, to take the current order in your data table:

=RankingValue - Row()/100000000

or this, to break ties based on a sub-value:

=RankingValue + ImportantScore/100000000