Pivot Table Filter - Sorting

A

AnnWillmott

New Member
Joined
Nov 17, 2008
Messages
9
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Right click on the Pivot Table - "Pivot Table Options" - Select the "Display" tab - The bottom section controls the "Field List" -Sort A to Z / Sort in data source order

Be sure to Refresh your Pivot Table
 
Upvote 0
Thank you. Yes, I've set Sort on the Display tab to A-Z, but that doesn't affect the filter drop-down menu, which is still out of order. It is a strange problem, but there literally seems to be no way to sort that drop-down menu.
 
Upvote 0
Hi Ann -
  1. Click the drop down arrow on the row header as if you were going to filter or sort your list<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
  2. Click on More Sort Options<o:p></o:p>
  3. Click on More Options (yes, there is another More Options button)
  4. Click to uncheck the AutoSort check box where it reads “Sort automatically every time the report is updated”
That should do it. Instead of automatically putting your filter list in alpha-numeric order Excel will let it match the order as it appears on your Pivot Table.

<o:p>Cheers,</o:p>
<o:p>Andrew</o:p>
 
Upvote 0
I've been having this problem too, the thing is there are no buttons when I click on the REPORT FILTER....
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
After messing around with it for hours, I think I found the easiest way; just unlink the data table and then relink it and refresh and you will have it sorted again!
 
Upvote 0
jeffwiesman said:
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.
Click to expand...

This one worked great for me! :)
 
Upvote 0
How do I unlink the data table? I'm having the same problem. I want to sort a field, but there are no options in 2007 to do this. I'm going to email Microsoft to fix this - it's really annoying - not that they will listen.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
192
Domenic
D
R
  • Locked
  • Question
Sorting list order in Slicer
Replies
1
Views
218
Peter_SSs
Peter_SSs
Gringoire
  • Question
Filtering on a pivot by dates (with some constraints)
Replies
0
Views
325
Gringoire
Gringoire
T
  • Question
Multiple filters on filtered list
Replies
1
Views
214
Cubist
Cubist

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top