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.
However, when you right-click a Report Filter field, those sorting and grouping options aren’t listed in the popup menu.
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.
Then, use the right-click popup menu to sort the date field in descending order.
Drag the field back to the Report Filter area, after you sort it.
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.
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.
_________
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.
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!
@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!
Thanks Debra. Of course, it worked:-)
Debra….Thank you so much for this great solution and saving me from a headache.