Sort Items in Excel Pivot Table Report Filter

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.

pivotreportfiltersort01

It’s easy to sort a Row field, but it takes a couple of extra steps to sort a Report filter.

Extra steps to sort a Report filter blog.contextures.com

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.

pivotreportfiltersort02

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.

pivotreportfiltersort04

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.
________________________

This entry was posted in Sort and Filter. Bookmark the permalink.

8 Responses to Sort Items in Excel Pivot Table Report Filter

  1. Andrea says:

    So simple, and yet I had no idea! Thanks!!

  2. Stephanie says:

    Thank you!! This was the perfect answer to my question, wish I would have started here!

  3. Ashish Pandey says:

    Thats really great tip. Thanks a lot.

    Keep posting such helpful tips…

  4. Denese says:

    Thank you for this tip – it was the only method that worked!

  5. Wendy says:

    Thank you for the great tip! I’m so glad I found your site.

  6. Pingback: Excel Roundup 20140317 - Contextures Blog

  7. Toby says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *