Sort Items in Excel Pivot Table Report Filter

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.

new item not in A-Z order
new item not in A-Z order

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.

Sort commands on the Ribbon’s Data tab
Sort commands on the Ribbon’s Data tab

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.

Sort commands on the Ribbon’s Data tab are dimmed
Sort commands on the Ribbon’s Data tab are dimmed

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.

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.

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

17 thoughts on “Sort Items in Excel Pivot Table Report Filter”

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

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

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

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.