Pivot Table Filter - Sorting

[AnnWillmott]

Hi, I have a pivot table that has a report filter field. (Excel 2007.) I am trying to figure out how to sort the filter's drop-down list. Field settings does not provide any sort options. PivotTable Options provides a sort specification for the entire table, but this doesn't seem to affect the filter.

The issue is that the filter is a number, and the numbers are out of order: 2203, 2204, 2213, 2205. In an attempt to troubleshoot this problem, I made sure the source data was all formatted as a number, not as text, but that didn't help.

The source data is also sorted by this number and 2213 appears in its proper place. So I can't figure out what is making Excel put the filter list in that order, and how to fix it. Anyone run across this and have words of wisdom? Any help will be greatly appreciated! --Ann

 

[ConcernedCitizen]

Hi, I have a pivot table that has a report filter field. (Excel 2007.) I am trying to figure out how to sort the filter's drop-down list. Field settings does not provide any sort options. PivotTable Options provides a sort specification for the entire table, but this doesn't seem to affect the filter.

The issue is that the filter is a number, and the numbers are out of order: 2203, 2204, 2213, 2205. In an attempt to troubleshoot this problem, I made sure the source data was all formatted as a number, not as text, but that didn't help.

The source data is also sorted by this number and 2213 appears in its proper place. So I can't figure out what is making Excel put the filter list in that order, and how to fix it. Anyone run across this and have words of wisdom? Any help will be greatly appreciated! --Ann

The suggestion to move the filter field to a row and then back to the filter works, although I found it limiting especially as I was trying to generate pivot table reports with a python script using openpyxl. One additional feature I added to the suggestion was to add an additional pivot table to the report (on a page that can remain hidden if desired) where the rows are the filter field. You may have to refresh your pivot tables, but in doing this step, since the data is shared across the pivot table it automatically orders the values in the filter fields the same way it does in the other table. Now if you are automatically adding new data to your workbook, simply by refreshing your hidden table will order the data, and the filter will be ordered as well.