Ranking within a pivot table

[pickytweety]

I have data with contestant names, their points for various events, various
categories for those events, etc. I've seen the AutoShow feature of the
Pivot Table that will sort the table based on total points, but what if I
wanted to see the actual place they took instead of their points. I want to
see a column with 1, 2, 3, 3, instead of 100, 99, 98, 98 and so on. Is there
a way to place a ranking inside the pivot table itself? Also, how do I keep
the fonts from changing when I select different events?

 

[Roger Govier]

Hi

I don't think you can achieve the ranking within the PT itself.
You could add another column to your source data and title it Rank
Assuming the Event name is in column C of your table and the total of all
points scored in that event is in column K,
then enter in the Rank Column the following array formula

{=SUM(IF(FREQUENCY(IF(($C$2:$C$17=C2)*
(K2<$K$2:$K$17),$K$2:$K$17),IF(($C$2:$C$17=C2)*
(K2<$K$2:$K$17),$K$2:$K$17))>0,1))+1}

To enter or modify an array formula, you need to use Control+Shift+Enter
(CSE) not just Enter.
When you use CSE, Excel will place the curly braces { } around the
formula. do not type these yourself.
I have spilt the formula across several lines to stop the newsreader
breaking it in awkward positions.
It is just one continuous formula.

Change the column letters to suit your data, then copy down as required.
Amend your data source for the PT to include the new column for Rank, and
use that in place of total points in the PT layout.

When you say "keep the fonts from changing" I am guessing you mean "keep the
format from changing".
Right click on any cell in the PT>Table Options>remove check mark from
Autoformat.