Excel 2007 : PivotTables and Order of Items in Drop-Downs

I have a set of pivot tables that I use for reporting test data for schools. When I replace the data in the file via cutting and copying to the datasource sheet and refresh my pivot tables, the tables function fine and refresh fine but the order of the items in the drop down lists of the various page fields that are on the tables is no longer in ABC order. I have been removing the fields from each pivot table (stored on separate worksheets), refreshing, adding the variables back to the page fields, refreshing and then the abc order is fine. Is there a way to prevent this when I reuse these files for a new school's data?

I have each table built from my first table in the file and changing the master table and refreshing all does not fix it nor update the data source for all files. I also have been having to go in and manually change the data source for each table as well after copying new data into my datasource.

I appreciate any guidance.

Hi LBM

I am not an expert on Pivot Tables (still learning) but if I understand your problem correctly, you need to:

Right click on the field, select field settings, then select advanced and then under the Autosort heading, change the selection from Manual to Ascending (or descending) depending upon your requirements.

Then click OK when done. You may have to refresh the Pivot Tables.

HTH ...spellbound

Spellbound,

Thanks for the quick note. I am in Excel2007 and when I right click on the field, select field settings, I don't have an advanced or any type of autosort showing. Looking around, nothing seems to fit from that option. Any thoughts?

It's in PivotTable Options, under the Display tab. You can have it sorted either A - Z or in data source order.
For customization, you can right click an element in the rows, then Move..., then select one of the options for moving it.

Sorry LBM, I am using Excel 2003, have no knowledge of 2007.

Hopefully one of the guys using Excel 2007 can solve your problem, although I would still expect 2007 to have some form of field properties option.

Just found something that may help but no way of checking it myself:

Go to Options Command button in Pivot Table Options group or press Alt+JTTT to open the dialog box. This box should contain 5 tabs, one of which is for Totals & Filters, which I believe may have means of sorting the table's fields.

I also understand that in Excel 2007 that the field's drop-down lists have a built-in option to sort them in either ascending or descending order.

HTH ...spellbound