In Excel, one of the PivotTable calculation options is to rank fields in a PivotTable.
Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.
Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.
We would like to add a rank field to the PivotTable for both fields.
Add a Rank Field in a PivotTable in Excel
- Add the field to the PivotTable that you want to use for ranking. In this case the No of Sales and Conversion Rate fields would be added again.
- Right mouse click on a value in the field you want to rank and select Show Value As > Rank Largest to Smallest.
- The ranking field is introduced. Repeat for any other fields of the table.
- Rename the column headings so that they no longer say Sum of **.
At this point the fields showing the value can be removed. The rank field can stand on its own and the other field is not necessary.