When you add new items to a pivot table, they might not appear in alphabetical order in the Report Filter drop down. This can make the items hard to find, if there is a long list.
In the drop down list below, Binder is a new product that was added to the source data. It appeared in the pivot table, after it was refreshed, but it’s at the bottom of the list, instead of the top.
It’s easy to sort a Row field, but it takes a couple of extra steps to sort a Report filter.
No Quick Sort for Report Filter Fields
If you right-click on a label in the Rows area, the popup menu shows the Sort commands. The Sort commands on the Ribbon’s Data tab are also available. In the screen shot below, I right-clicked on the Monday label, and can sort the Weekdays field quickly, with the popup menu A-Z or Z-A commands.
You can read more about sorting Row fields, and see a short video in this recent post.
However, when you right-click on a Report Filter field, the popup menu doesn’t have a Sort command. Also, the Sort commands on the Ribbon’s Data tab are dimmed, so you can’t click them.
Move the Report Filter Field
To sort the Report Filter field, you can move it temporarily. In this example, we’ll sort the Product field.
- Drag the Product field to the Rows area, above all the existing Row fields
- Right-click on one of the Product field labels
- Click the Sort A-Z command, to sort the field items
- Drag the Product field back to the Report Filter area.
Watch the Video
Watch this very short video, to see how to show the items alphabetically, by temporarily moving the Report Filter field to the Rows area in the pivot table.
Or watch on YouTube: Sort Pivot Table Report Filters in Alphabetical Order
Download the Sample File
To download the sample file, please visit the Pivot Table Sorting page on my Contextures website.