To sort an Excel PivotTable by count, place the field you want to count in the Values area, set its summary to Count, then right-click a count in the PivotTable and choose Sort > Sort Largest to Smallest. Excel reorders the row labels according to that count, so the most frequent categories appear first.
Set up the count in the PivotTable
A PivotTable can sort categories by a count only when that count is available as a value field. For example, if your source data has an Order ID column and a Category column, put Category in Rows and Order ID in Values. Excel will usually summarize a text ID as Count of Order ID; if it shows a different calculation, change it to Count.
- Click a cell in the source data, then choose Insert > PivotTable and create the PivotTable.
- In the PivotTable Fields pane, drag the category field, such as Category, into Rows.
- Drag a field that is populated for each record, such as Order ID, into Values.
- If needed, open the field’s menu in Values, choose Value Field Settings, select Count under Summarize Values By, and select OK.
The result might show categories such as Books, Furniture, and Supplies alongside counts of their records. You are counting nonblank entries in the chosen value field, not necessarily measuring a unique number of orders. Choosing a reliable, consistently populated field is therefore important.
Sort categories from most to fewest
Once the count appears in the PivotTable, sort using a count cell—not a row label or the Grand Total. Click any ordinary number in the count column, right-click, and choose Sort > Sort Largest to Smallest. Excel sorts the corresponding row items by that value. To put the least frequent items first, choose Sort > Sort Smallest to Largest.
There is also a dialog-based route that lets you name the value field explicitly. Open the drop-down beside Row Labels (or the relevant row field), choose More Sort Options, select Descending (Z to A) by, and select the count field in the list. The wording refers to descending order; for a numeric count, that means larger counts come first. The available controls can look slightly different depending on the PivotTable layout.
- Largest to smallest: use this for a frequency ranking, such as the most common product categories.
- Smallest to largest: use this to find the least frequent categories, including possible data-quality issues.
- Alphabetical sort: sorts labels by their names, not by the count. If the result looks alphabetical, repeat the sort using a count cell or More Sort Options.
What Count means in Excel
In a PivotTable, the Count summary counts nonempty entries in the selected field for each group. If Category is in Rows and Order ID is in Values, the count for each category is the number of source records in that category with a nonblank Order ID. It is not automatically a count of distinct IDs. If an ID occurs on several source rows, each nonblank occurrence contributes to the count.
This is different from a sum, which adds numeric values, and from an average, which calculates a mean. It is also worth distinguishing PivotTable Count from the worksheet COUNT function: that worksheet function counts numbers, whereas the PivotTable Count summary can count populated text as well as numeric entries. Either way, blank cells in the chosen field are not counted. If some records have a blank Order ID, those records will not contribute to that category’s value.
To count records even when one particular field can be blank, choose another field that is filled on every row, such as a dependable record key. If no suitable field exists, clean or supplement the source data so that the field being counted represents the records you intend to include.
Worked example: rank categories by record frequency
Suppose a sales export has three columns: Order ID, Date, and Category. You want to see which category occurs most often. Create a PivotTable with Category in Rows and Order ID in Values, summarized by Count. If the resulting counts are Books 18, Furniture 7, and Supplies 12, sorting Largest to Smallest should display Books, Supplies, then Furniture.
Those numbers mean there are 18, 12, and 7 counted source entries in the respective categories, provided the Order ID field is populated on those rows. They do not prove there were 18, 12, and 7 distinct orders if an order can contain multiple category lines. If the question is “How many unique order IDs included each category?”, you need a distinct count, not the ordinary Count summary. The distinction matters whenever the source has repeated IDs.
A quick check is to compare the category counts with what the source represents. If each source row is one item line, the PivotTable counts lines. If each row is one order, it counts orders with a populated key. PivotTables summarize the rows they receive; they do not infer the business meaning of a record.
Sort when the PivotTable has several fields
With more than one field in Rows, a PivotTable is hierarchical. For example, placing Region above Category creates categories nested under each region. Sorting a field by a value may order its items within the relevant parent groups, rather than creating one flat ranking of every category across all regions. Confirm the layout and the selected row field when interpreting the result.
If you have multiple fields in Values—perhaps Count of Order ID and Sum of Sales—choose the intended measure when using More Sort Options. A sort based on sales can produce a different order from a sort based on record count. Likewise, selecting a cell in the wrong value column before using the right-click sort command can rank the labels by the wrong measure.
For a simple overall ranking, keep the category field as the only row field and use the count as the sort measure. If you need a ranking within each region, retain the hierarchy and sort the category field by the count. If you need a single list of category totals across regions, remove Region from Rows or place it in Filters, then sort the resulting category list.
Common problems and how to fix them
- The values show Sum instead of Count. Open Value Field Settings for the field in Values and select Count. Excel may choose Sum automatically when it recognizes the source field as numeric.
- Count is missing or gives an unexpectedly small result. Check whether the field has blanks. Select a field populated on every record, or correct missing source values if appropriate.
- The labels sort alphabetically. Start from a numeric count cell and use Sort > Sort Largest to Smallest, or choose More Sort Options and specify the count field.
- The wrong measure controls the order. If the PivotTable has multiple value columns, select a count cell or explicitly select the count field in More Sort Options.
- The counts do not match the number of unique IDs. Ordinary Count includes repeated nonblank entries. Use a distinct-count method if uniqueness is the question.
- New source rows do not appear. Refresh the PivotTable and confirm that its source range includes the new rows. Formatting the source as an Excel Table before creating the PivotTable helps the source range expand as rows are added.
When the source is an Excel Table, add records within or directly below the table so that the table can expand, then refresh the PivotTable. If the PivotTable uses a fixed cell range, use PivotTable Analyze > Change Data Source to check or update that range, then refresh. A refresh updates the summary from its source; it does not correct a mistaken field choice or turn a count into a distinct count.
Count versus distinct count and top-item filters
Use ordinary Count when each nonblank source row should contribute once. Use Distinct Count when repeated values should contribute only once within each group—for example, unique customers per product category. In Microsoft 365 for Windows, a PivotTable created with its data added to the Data Model can offer Distinct Count in the value summary choices. Availability and Data Model support differ across Excel editions and platforms, so that option may not appear in every Mac or web setup. If it is unavailable, use a workflow that can calculate unique group counts rather than treating ordinary Count as equivalent.
Sorting also differs from filtering. Sorting Largest to Smallest keeps all categories and changes only their order. If you want to show only the leading categories, use the row field’s drop-down and choose Value Filters > Top 10, then set the number of items and the value field to use. The default wording may say “Top 10,” but the dialog lets you choose a different number and can be configured for bottom items. A filter hides other categories from the current view; it does not delete them from the source.
Windows, Mac, and Excel for the web
These steps describe Excel for Microsoft 365 on Windows. On Windows, the right-click menu is usually the fastest way to sort, while the Row Labels drop-down and More Sort Options provide more control. Ribbon labels and the PivotTable Fields pane may vary slightly with the selected cell and the current layout.
Excel for Mac also supports sorting PivotTable items by values, but the context menu and ribbon placement can differ by build. Try Control-clicking or right-clicking a count cell, then look for the sort options; if they are not shown, use the row field’s menu and its sort options. Excel for the web has PivotTable capabilities too, but some advanced options and Data Model features are not available in every browser version. When an option is absent, open the workbook in the desktop app rather than assuming the count itself is unsupported.
Frequently Asked Questions
Can I sort a PivotTable by count without displaying the count?
Usually, the count must be present as a value field for Excel to use it as the sort measure. You can keep that value field in the PivotTable while adjusting the layout or formatting, but removing it may remove the measure needed for the sort.
How do I sort a PivotTable from highest count to lowest?
Click a count value, right-click, and choose Sort > Sort Largest to Smallest. If the result is not right, use More Sort Options and explicitly choose the count field.
Does PivotTable Count include blank cells?
No. Count summarizes nonempty entries in the selected field. Pick a different field if the one you are counting contains blanks in records that should be included.
Why does my count differ from the number of unique customers?
Ordinary Count counts populated source entries, including repeated customer IDs. A unique-customer total requires Distinct Count or another method that removes duplicates within each group.
Will a PivotTable keep its count sort after refresh?
It often retains the sort setting, but check the order after refreshing, especially if new categories or values have appeared. A refresh updates the summary; it cannot include rows outside a fixed source range unless you update that range.
Related
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Legend Not Showing All Labels?If an Excel chart legend is missing labels, first check what the chart treats as a legend entry:…
- How to Save a Graph in Excel as a Picture: PNG or JPGTo save a graph (which Excel calls a chart) as an image in Microsoft Excel for Microsoft 365…
- Excel Column Chart Colors Based on Values: How to Set ThemTo color an Excel column chart by value, use separate helper series for the value ranges you want…
- Google Sheets Log Scale Charts: Setup and TroubleshootingTo make a log scale chart in Google Sheets, select your data, choose Insert > Chart, then open…
- Excel Pivot Table: How to Turn Off SubtotalsTo turn off every subtotal in an Excel PivotTable, click inside it and choose PivotTable Design > Subtotals…
- Excel Horizontal Bullet Chart: Build One Step by StepTo make a horizontal bullet chart in Excel for Microsoft 365 on Windows, create a stacked bar chart…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.