Pivot Table Issues

How to Sort an Excel PivotTable by Count in Descending Order

Kushani Nimanthika Oct 21, 2026 868 views

Question details

The user needs to sort a PivotTable based on the numerical values in a Count field rather than the default alphabetical order of the row labels.

How to Sort an Excel PivotTable by Count in Descending Order

Product: Excel
Device & OS: not provided
Scenario: Organizing PivotTable data to quickly highlight the highest frequencies or counts of items.
Observed behavior: The PivotTable defaults to sorting alphabetically by row labels, but the desired state is to display records ordered sequentially from the highest count to the lowest.

Before you start

Ensure your PivotTable is already created and that the field you want to sort by has been added to the Values area and configured to summarize by 'Count'.

Solution 1Recommended

Sort PivotTable Values Using 'More Sort Options'

The most reliable way to sort a PivotTable by count and have the rule stick is to use the built-in advanced sorting options applied directly to the value field.

By default, PivotTables sort by row labels. To override this, you must apply a specific sort rule tied to the value column. This ensures that even if your data changes and the PivotTable is refreshed, the highest counts will continually remain at the top.

Open the Row Labels dropdown

Click the small dropdown arrow next to the 'Row Labels' header in your PivotTable.

Access advanced sort settings

Select 'More Sort Options' from the bottom of the dropdown menu.

Choose descending order

In the Sort dialog box, choose the 'Descending (Z to A) by:' radio button.

Select the Count field

Click the dropdown menu below the Descending option and select your specific value field, typically named 'Count of [Field Name]'.

Apply the sort

Click 'OK'. The row labels will dynamically rearrange to display from the highest count to the lowest.

Sort PivotTable Values Using 'More Sort Options'

Persistent Sorting: Using this method sets a permanent sorting rule. When you add new data and click 'Refresh', your PivotTable will automatically re-sort to keep the highest counts at the top.

Solution 2

Quick Sort Using the Right-Click Menu

If you need a fast, one-time sort, you can instantly order your data by right-clicking directly inside the numerical values column.

Manage PivotTables Seamlessly

Create and Sort Pivot Tables Easily with WPS Spreadsheet

WPS Office features a powerful, user-friendly Spreadsheet application that fully supports PivotTables. You can easily analyze complex datasets, customize value summaries like 'Count', and sort your data precisely how you need it without any hassle.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, select your data range, go to the 'Insert' tab, and click 'PivotTable'.
2. Configure the Count value: Drag your target field into the 'Rows' area, and drag the same field into the 'Values' area. Ensure it is set to summarize by 'Count'.
3. Sort the data: Right-click any number in the generated Count column, hover over 'Sort', and click 'Descending' to organize your data from highest to lowest.

Fully compatible with Microsoft Excel (.xlsx) formats and existing PivotTable structures.Intuitive interface for effortless drag-and-drop PivotTable field management.Built-in advanced sorting, filtering, and data analysis capabilities out of the box.Free and lightweight, ensuring smooth data processing on Windows, Mac, and Linux devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my PivotTable sorting alphabetically instead of by value?

By default, PivotTables sort data by the Row Labels in alphabetical order. To change this behavior, you must explicitly tell the PivotTable to sort by a specific value column using the 'More Sort Options' menu or by right-clicking a cell in the values column.

How do I change the summary type from Sum to Count before sorting?

Right-click any cell in the Values area of your PivotTable, select 'Summarize Values By' (or 'Value Field Settings'), and then click 'Count'. Once the data reflects counts, you can proceed to sort the column in descending order.

Can I automatically sort a PivotTable so new data stays sorted?

Yes. When you use the 'More Sort Options' menu from the Row Labels dropdown and set the sort order to 'Descending' by your specific count field, the PivotTable will remember this rule. It will automatically re-sort your data according to the new counts whenever you hit 'Refresh'.

Why is the 'More Sort Options' button grayed out?

This usually happens if you have selected multiple cells or if you are clicking inside the Grand Total row. Ensure you only click the single dropdown arrow in the Row Labels header, or select a single standard cell within the values area.

Excel & Data Problems