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
If you only have one or two Report Filter fields to sort, you can use the following steps, to do the sort manually.
- NOTE: If you have several Report Filter fields to sort, use the Report Filter sorting macro, on my Contextures site.
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.
Download the Sample File
To download the sample file, please visit the Pivot Table Sorting page on my Contextures website.
And for macros, go to the Pivot Table Report Filter Macros page on my Contextures site.
________________________
So simple, and yet I had no idea! Thanks!!
Thank you!! This was the perfect answer to my question, wish I would have started here!
Thats really great tip. Thanks a lot.
Keep posting such helpful tips…
Thank you for this tip – it was the only method that worked!
Thank you for the great tip! I’m so glad I found your site.
Thanks Debra, I’m a seasoned excel user and sorting field lists has always been an annoyance.
Your tip is beautiful in its simplicity! Move the field to where you can sort it – can’t believe I never thought of it!
Thanks again and all the best, Toby
Thanks Toby!
Thank you! This has always been a sore spot for me in Excel Pivot Tables.
Thanks for letting me know that it helped!
This is an old post but the tip still works in 2019 for Excel Professional Plus 2013.
The tip is great. However, this does not work if you have data below the pivot table. By adding multiple rows, it will overwrite any data you might have below.
The tip IS great. However, if I have a pivot table that refreshes often, that has six filters in it… I have to drag all six down into the table, sort, then drag them back? Every time I update the table? That’s…silly. I wonder why Microsoft designed it this way..
This was exactly what I needed!! Thank you!
Thank you! This has been driving me nuts in a few different spreadsheets I have.
Doesnt help when you have 8 – 10 pivot tables and each table has 2 – 3 filters you cant manually keep on doing it everytime data is changed.
There’s a Report Filter sorting macro on my Contextures site that would make the job quicker:
https://www.contextures.com/excelpivottablereportfiltervba.html#sort