Yes, you can sort a pivot table by the Count column. Right-click any number in the Count column, choose Sort, then select either Sort Smallest to Largest or Sort Largest to Smallest. This works in Excel for Microsoft 365, Excel 2021, and earlier versions, on both Windows and Mac.
Why the Count Column Needs Special Handling
In a pivot table, the Count column shows how many rows fall into each category. Unlike a Sum column, which adds numeric values, Count simply tallies records. When you first build a pivot table, the row labels are usually sorted alphabetically or by the order you dragged fields in. That default order rarely matches what you want for a frequency analysis. Sorting by the Count column lets you instantly see which category appears most or least often, which is the core purpose of a frequency table.
A common misconception is that you can sort by count by clicking the header arrow in the pivot table. That arrow only sorts row labels alphabetically or by the default data order. To sort by the numeric count, you must use the right-click menu on a value cell inside the Count column. This is a frequent source of confusion for users new to pivot tables.
Step-by-Step: Sorting by Count
- Click anywhere inside the pivot table to activate the PivotTable Analyze and Design tabs on the ribbon.
- Locate the Count column in the Values area. It usually appears as “Count of [Field Name]”.
- Right-click on any single number within that Count column (for example, the cell showing “12”).
- Hover over Sort in the context menu.
- Choose Sort Largest to Smallest to see the highest counts first, or Sort Smallest to Largest to see the lowest counts first.
On a Mac, the right-click menu is identical. If you use a trackpad, Control-click works the same as right-click. In Excel for the web, right-clicking a value cell also reveals the Sort options, though the menu may look slightly different.
Sorting When the Count Column Is Not the Last Column
If your pivot table has multiple value fields (for example, Count of Orders and Sum of Revenue), the Count column might not be the rightmost column. The right-click sort method still works, but you must right-click a cell in the specific Count column you want to sort by. Excel sorts the entire pivot table based on that column’s values, not just the visible cells. This can be disorienting if you expect row labels to stay in their original order—they won’t; they reorder to match the sort.
For example, suppose you have a pivot table with Product in rows, Count of Sales and Sum of Profit in values. If you right-click a number in the Count of Sales column and choose Sort Largest to Smallest, the Product row labels reorder by sales count, not by profit. To sort by profit instead, you would right-click a cell in the Sum of Profit column.
Using the Sort Dialog for More Control
For finer control, you can use the Sort dialog box instead of the quick right-click menu. Here’s how:
- Right-click a cell in the Count column and choose More Sort Options (or on Mac, choose Sort then More Options).
- In the Sort dialog, select Descending (Largest to Smallest) or Ascending (Smallest to Largest).
- Under “Sort By”, select the specific value field if you have multiple. The default is the field you right-clicked.
- Click OK.
This dialog also lets you choose whether to sort by the actual numeric value or by the percentage of the grand total. If your pivot table shows counts as percentages (via Show Values As), the dialog will let you sort by that percentage. The quick right-click menu only sorts by the raw count, not by percentage, so the dialog is essential for percentage-based sorting.
What If the Count Column Shows Blanks or Zeros?
Blanks in a Count column usually mean there are no underlying records for that row label. For instance, if you count orders by region and the “South” region has no orders, the Count cell shows 0 (or blank if you’ve hidden zero values). These rows will sort to the bottom when you choose Largest to Smallest, and to the top when you choose Smallest to Largest. If you want to exclude blank rows entirely, use the pivot table’s filter dropdown on the row label and uncheck “(blank)”.
Another issue: if your source data contains empty cells in the field you’re counting, Excel’s default Count function counts non-empty cells only. If you want to count empty cells too, you need to use the Count Numbers or Count Averages function, but those are rarely what you want for a frequency table. For a true count of all rows, including blanks, use “Count” on a field that has no blanks, such as an ID column.
Sorting After Refreshing the Pivot Table
When you refresh a pivot table (right-click > Refresh), the sort order you applied is preserved. Excel remembers the sort rule you set via the right-click menu. However, if you change the pivot table structure—for example, adding a new field to the Rows area or removing a field from the Values area—the sort may reset to the default alphabetical order. To reapply the sort, simply right-click the Count column again and choose the same option. This is a common annoyance, but it’s easy to fix.
If you want the sort to be permanent, consider using a custom sort order in the source data and then setting the pivot table to “Sort by Row Label” with a custom list. That’s more complex and rarely necessary for a simple count sort.
Sorting by Count in a Pivot Chart
If you’ve created a pivot chart from your pivot table, the chart’s category axis order follows the pivot table’s row label order. So, if you sort the Count column in the pivot table, the chart updates automatically to reflect the new order. This is a great way to create a bar chart that shows categories from highest to lowest frequency without any extra steps.
One caveat: if you have a pivot chart with multiple series, the sort applies to the entire pivot table, so all series in the chart will reorder together. You cannot sort one series independently without affecting the others.
Frequently Asked Questions
Why can’t I sort by the Count column using the header arrow?
The header arrow on a pivot table field only sorts row labels alphabetically or by the underlying data order. It does not interpret the numeric values in the Count column. To sort by the count value, you must right-click a cell inside the Count column and use the Sort menu. This is a deliberate design choice in Excel to avoid confusion between label sorting and value sorting.
How do I sort by count if my pivot table has multiple value fields?
Right-click a cell in the specific Count column you want to sort by. Excel will sort the entire pivot table based on that column’s values. If you have both a Count and a Sum column, make sure you right-click the Count column, not the Sum column. The Sort dialog also lets you explicitly choose which value field to sort by, which is helpful when columns are not adjacent.
Will the sort order stay after I refresh the data?
Yes, refreshing the pivot table preserves the sort order you set via the right-click menu. However, if you change the pivot table’s structure—such as adding or removing fields—the sort may reset. To restore it, just right-click the Count column and reapply the sort. If the sort keeps resetting, check that you are not accidentally dragging fields around, which can trigger a re-sort.
Can I sort by count in Excel for the web?
Yes, Excel for the web supports right-click sorting on pivot table value cells. Right-click a number in the Count column, hover over Sort, and choose the desired order. The interface is slightly more compact, but the functionality is the same. The Sort dialog with “More Options” is also available in the web version.
Why does my Count column show “Count of Name” instead of just “Count”?
That’s the default label Excel generates when you drag a field into the Values area and it recognizes it as text or a non-numeric field. The label “Count of Name” means it’s counting the number of non-empty cells in the “Name” field. You can rename it by clicking the label in the pivot table and typing a new name, but the underlying function remains a count. This is normal and does not affect sorting.
How do I sort by count when there are blank rows in the source data?
Blank rows in the source data can create a “(blank)” row label in the pivot table. That row will have a count of the number of blank rows, which might be zero or more. To exclude it from the sort, use the row label filter to uncheck “(blank)”. Then sort the Count column as usual. If you want to count blank rows as valid categories, you’ll need to fill them with a placeholder value like “Unknown” in your source data before creating the pivot table.
Related
- Excel Learning Center Wilmington NC: Classes & Training
- Excel Just Numbers From a Cell? How to Extract Text
- Excel Learning Center Jacksonville NC: Classes & Training
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Gantt Chart in Excel: Templates, Setup, and Scheduling TipsYes—Microsoft Excel for Microsoft 365 includes online project-planning templates you can search from File > New, and you…
- Excel Chart Design Not Working? Fix the Common CausesIf Excel Chart Design is missing or its options are not working, click the chart first to show…
- Pivot Table Charts in Excel: Create, Format, and UseYes. In Microsoft Excel for Microsoft 365 on Windows, click inside a PivotTable, choose PivotTable Analyze > PivotChart,…
- Excel Pivot Table Filters: How to Exclude Matching ItemsTo exclude PivotTable items containing particular text, open the dropdown for a row or column field, choose Label…
- Excel Sparklines Formula: How to Create and Customize ThemExcel does not have a worksheet formula called SPARKLINE: a sparkline is a small chart inserted into a…
- Google Sheets Gantt Chart Formatting GuideTo format a Gantt chart in Google Sheets, arrange tasks in rows, put each task’s start and end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.