Pivot Table Issues

How to Rank Players Correctly in an Excel PivotTable with Teams

Chanuka Geekiyanage Oct 23, 2026 868 views

Question details

The user needs to correctly rank players in a PivotTable when both the Team and Player fields are applied, but currently, all players display a rank of one.

How to Rank Players Correctly in an Excel PivotTable with Teams

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a PivotTable with multiple row labels (Team and Player) to rank individual players by their performance metrics within their respective teams.
Observed behavior: The PivotTable assigns a rank of '1' to every player. The ranking calculation only works correctly when the Team field is completely removed from the row labels.

Before you start

Before adjusting the PivotTable settings, ensure your source data contains clear column headers with no blank rows, and verify that the metrics you intend to rank are formatted as numbers.

Solution 1Recommended

Adjust the Base Field in Value Field Settings

Correct the ranking behavior by specifying the correct Base Field in the PivotTable's value settings, ensuring the calculation applies to the player level rather than the team level.

When you use multiple row labels in a PivotTable, the default ranking might evaluate the top-level parent category (Team) rather than the sub-category (Player). Adjusting the Base Field forces the PivotTable to compare the players against each other.

Access Value Field Settings

Right-click on any cell in the PivotTable's value column where the rank is currently displaying '1', and select 'Value Field Settings' from the context menu.

Navigate to the Show Values As tab

In the Value Field Settings dialog box, click on the 'Show Values As' tab at the top.

Select the Ranking calculation

Click the 'Show values as' dropdown menu and select 'Rank Largest to Smallest' (or 'Rank Smallest to Largest', depending on your preference).

Set the correct Base Field

Under the 'Base field' list, click on 'Player' instead of 'Team'. Click 'OK' to apply the changes. The PivotTable will now correctly rank each player.

Adjust the Base Field in Value Field Settings

Ranking Within Teams: By setting the Base Field to 'Player', the PivotTable will automatically calculate the ranks for players grouped within their respective teams.

Solution 2

Reorder Row Labels to Prioritize Player Ranking

Rearrange the hierarchy in your Row Labels area to ensure the PivotTable calculates item hierarchies appropriately.

Powerful Data Analysis

Easily Rank and Analyze Data with WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive PivotTable features, allowing you to correctly rank data across multiple categories without the hassle. Follow these steps to set up your ranking directly in WPS Office.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, select your data range, go to the 'Insert' tab, and click on 'PivotTable'.
2. Set up Row Labels: In the PivotTable Fields pane, drag 'Team' and then 'Player' into the 'Rows' area to establish the hierarchy.
3. Add values to rank: Drag the numerical metric you want to rank into the 'Values' area.
4. Modify Value Field Settings: Right-click the value column in the PivotTable, select 'Value Field Settings', and switch to the 'Show Values As' tab.
5. Apply Base Field Ranking: Select 'Rank Largest to Smallest' from the dropdown, choose 'Player' as your Base field, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) file formatsIntuitive PivotTable creation and managementAdvanced sorting and 'Show Values As' settings for accurate rankingFree and lightweight spreadsheet tool

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable rank everyone as 1?

This happens when the PivotTable is ranking based on the parent group (like Team) instead of the individual sub-items (like Player). Because there is only one 'Player' record under that specific line's context if the base field is set incorrectly, they all tie for first place. Changing the Base Field to the correct sub-item resolves this.

Can I rank players within each team separately?

Yes. By grouping your rows with 'Team' first and 'Player' second, and then setting the ranking Base Field to 'Player', the PivotTable will automatically reset the ranking calculation for each new team, effectively ranking players within their respective teams.

What if the 'Show Values As' option is missing or grayed out?

Ensure your data source is in a standard table format without errors. More importantly, verify that the field you are trying to modify is actively placed in the 'Values' area of the PivotTable Field List, rather than just the Rows or Columns areas.

Excel & Data Problems