Sorting Pivot Table Report Fields

To focus on specific data in an Excel pivot table, you can add a field to the Report Filter area. Then, select one or more items from that pivot table field, to see the summarized data.

If you put a date field in the Report Filter area, there might be a long list of dates in the dropdown list. When you create the pivot table, the dates are usually in ascending order, with the oldest dates at the top of the list.

That’s not too convenient, if you want to focus on the latest data, instead of the oldest. How can you change the sort order, to put the newest dates at the top of the list?

Sort the Report Filter Field

Usually, it’s easy to sort a pivot table field. For example, if you right-click on a field in the Row area, the popup menu shows sorting and grouping options.

PivotFilterGroup01

However, when you right-click a Report Filter field, those sorting and grouping options aren’t listed in the popup menu.

PivotFilterGroup02

Move the Pivot Table Field

To solve the sorting problem, you can use a workaround – temporarily move the Report Filter field to the Row Labels area.

PivotFilterGroup03

Then, use the right-click popup menu to sort the date field in descending order.

PivotFilterGroup04

Drag the field back to the Report Filter area, after you sort it.

PivotFilterGroup05

Report Filter Dates in Descending Order

After you sort the field in descending order, you’ll see the latest dates at the top of the drop down list.

PivotFilterGroup06

It’s not the ideal solution, but it works! Maybe in the next version of Excel you’ll be able to sort the fields, without moving them from the Report Filter area.

Video: Sort Report Filter Field

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.

_________

5 thoughts on “Sorting Pivot Table Report Fields”

  1. Thank you Thank you! very helpful with the sorting the pivot fields….man, I was trying to find a solution to this as opposed to having to regenerate the pivot fields all the time. This looks like it did the trick.

  2. Debra, just how many blogs do you have? I just got here from a great Pivot Table FAQ page I’d never seen before, which had an interesting tip for changing a pivot chart without changing the table.

    The other day I found your blog about blogging, with the useful CodeColorer post.

    It’s like an entire Contextures queendom!

    1. @Doug, oh, I’ve lost count of the blogs, and thanks for asking. 😉

      The Contextures website and blog are still the main focus, and this Pivot Table blog lets people find articles on that topic more easily.
      Excel Theatre is where I post the daily Excel tweet collections, and Spreadsheet Day is for celebrating all kinds of spreadsheets.
      My Debra D’s Blog is for stuff that doesn’t quite fit anywhere else!

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.