Excel Pivot Table Can't Access Average of Rank

Question

In regards to making a Pivot Table. When I'm trying to use the Top 10 filter, I am unable to even see the option for Average by Rank, only Sum of Rank. What is preventing me from being able to select it?

Answer 1

To add a column with Average by Rank in your pivot table, follow these steps:

• Add data twice in the pivot table for which you want to measure the ranking.

For example, if you're trying to do this with a column labelled Quantity, add a second column next to it labelled Quantity2 (or whatever).

• Select a cell in the second data column and right-click on it
• Choose Show Values As and select the ranking option (e.g. Rank Smallest to Largest...) you want

This adds a ranking to your pivot table.

• Now sort your data as you wish

Note: Obviously, ranks will change when you filter your Pivot Table.

If I've misunderstood your question, please feel free to clarify.