Pivot Table Issues

How to Sort and Rank Items Numerically in an Excel Pivot Table

Olivia Miller Oct 8, 2026 871 views

Question details

The user needs to sort items in a PivotTable by their numerical ranking instead of their alphabetical names.

How to Sort and Rank Items Numerically in an Excel Pivot Table

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing and presenting team rankings or numerical data in a logical order within a PivotTable.
Observed behavior: The PivotTable defaults to sorting row labels alphabetically, causing items with the highest numerical rank to appear out of order.

Before you start

Ensure your PivotTable is already generated and includes a value field containing the numerical rankings you want to sort by.

Solution 1Recommended

Sort PivotTable by Numerical Ranking using More Sort Options

This is the most direct method to change the default alphabetical sort to a numerical sort based on a specific value field.

By default, Excel organizes row labels in alphabetical order. To arrange them by their actual performance or ranking, you need to apply a value-based sort.

Select the PivotTable

Click anywhere inside your existing PivotTable to activate it.

Open Row Label Options

Click the drop-down arrow next to the row-label field heading (e.g., Team Names) that you want to reorder.

Access Advanced Sort Settings

In the drop-down menu, select 'Sort', and then click on 'More Sort Options'.

Apply Numerical Sorting

In the Sort dialog box, select 'Ascending (A to Z) by' or 'Smallest to Largest'. Then, from the drop-down list below it, choose the value field that contains your ranking numbers and click OK.

Sort PivotTable by Numerical Ranking using More Sort Options

Sort Applied Successfully: The PivotTable will immediately refresh to display the teams perfectly ordered by their numerical ranking.

Efficient Pivot Table Management

Sort and Rank Data Easily with WPS Spreadsheet

WPS Office Spreadsheet provides robust and intuitive PivotTable features, allowing you to organize, rank, and analyze your data seamlessly. It fully supports advanced sorting options, making it a perfect tool for numerical data management.

1. Open your Workbook in WPS: Launch WPS Spreadsheet and open the file containing your raw data.
2. Create a PivotTable: Go to the 'Data' or 'Insert' tab, click 'PivotTable', and set up your rows and values, ensuring your ranking field is placed in the Values area.
3. Sort the Row Labels: Click the drop-down icon on your Row Label, select 'More Sort Options', choose your ranking value field, and set it to 'Ascending'.

Fully compatible with Microsoft Excel (.xlsx) PivotTables and formatting.Intuitive drag-and-drop interface for quick PivotTable creation and sorting.Lightweight software that processes large datasets with rankings rapidly.Free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable automatically sort items alphabetically?

By default, PivotTables sort row and column text labels in alphabetical order (A-Z) to organize data logically. You must manually modify the sort settings if you want to order items by a specific numerical value or ranking field.

Can I manually rearrange items in a PivotTable without sorting numerically?

Yes. You can manually drag and drop row labels to reorder them. Simply click on the border of the cell containing the label you want to move, and drag it to your preferred position within the PivotTable.

What if my ranking field is not showing up in the More Sort Options?

Ensure that the ranking field has been added to the 'Values' area of your PivotTable Fields pane. Only the fields currently being summarized in the Values area will appear as options for value-based sorting.

Excel & Data Problems