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

 

[northdakota103a]

I tried to change the data source and refresh -but that didn't work. Any other ideas. Tried moving field setting to column/row heading and it won't move - states it is a merged area.

Oy, vey? Anyone - please!

 

[xenou]

"Unlink" might me take all fields out, refresh, and put the pivot table back together again. Or simply, delete the whole table by deleting the cells it is in, then put it back in - its usually a little trouble but not too hard since pivot tables are easy to create. I'm not so well versed in 2007 pivot tables - they seem to have changed a bit.

 

[AnnWillmott]

Yes, as others found, Jeff's solution works! Not elegant, but fixes the problem. Thanks!

One way I found to do it is to move the field from "Report Filter" to "Row Label". Then right-click on one of the new row labels and select the desired sort option. Then move the field back to the "Report Filter" area. The sort options are retained.

 

[Mi5terH]

Thanks all. Came across this same problem myself today in Excel 2010 and using the suggestions above as a guide I have found that the following VBA solution also seems to work quite nicely:

ActiveSheet.PivotTables("Pivot1").PivotFields("FIELD NAME").AutoSort xlAscending, "FIELD NAME"

To use this method in VBA replace "Pivot1" with the name of your pivot table and "FIELD NAME" with the field name of the filter you are using. Simples.

 

[Number4]

Old thread, but I ran into this issue today. Here's my fix. (using Excel 2013)

Click in body of Pivot table. In the Pivot Table Fields box, I unchecked the item that was in the FILTERS box.
Then clicked on the first cell in the column I wanted to sort.
Right Clicked > Sort > More Sort Options > More Sort Options (yes, it's there twice)
Check then uncheck the AutoSort box (this gave me access to the Sort By box below it.)
Click in the range box under Sort By, highlight range/input range you wish to sort.
Click Ok, then Ok. You should be back in your spread sheet and it should be sorted.
Go back to your PivotTable Fields box and re-insert the item you want as a Filter.

 

[terrapin]

Thanks holsen - that works.

 

[CPM86]

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

I had this issue and found that the sorting anomaly was due to a single blank cell in the source data. When I changed the blank cell to a 0, the pivot table filter sorted normally.

 

[arungoin]

Thank you Holsen - It worked for me.

 

[arungoin]

I agree, there is no way to right click on the filter arrow and get the More Sort Options choice, however, if you pull the report filter field into the Row Labels you can then right click on the filter arrow and choose More Sort Options. Then you can select A-Z or Z-A. When you are done just pull the field back up into the report filter.

Thank you Holsen - It worked for me.

 

[sundarr]

it's working. thank you very much for your valuable information.