Excel standard list problem

[gr43023]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm using a dataset that includes three-letter codes for locations. One of the locations is abbreviated SAT. In pivot tables Excel insists on treating SAT as a date, and it always appears first in tables sorted in alphabetical order, rather than between the 'R' and the 'T' locations.

I found the pane for list management in Excel preferences, but it won't allow me to edit or delete the four pre-programmed lists. Is there any way to persuade Excel to treat SAT as a three-letter abbreviation rather than a day of the week?

 

[CyberTaz]

When you select that field to sort by go to Data> Sort, click the Options
button & pick Normal from the drop-down.

[SAT is being recognized as a weekday abbreviation, BTW, not a Date ]

There is a default custom sort available to sort by one of the items in the
list but that isn't the default mode. Apparently your setting got changed
somewhere along the line

As you found, you can't delete the default custom sorts but you can add
lists of your own: Let's say you have operations in OH, AK, PA, MD & DE and
you want to be able to sort the records in that order rather than
alphabetically. Create the custom list in the order you want to sort by
using the Custom Lists manager to Add it to the list. Then use the Data>
Sort> Options to take advantage of it when needed.

Just keep in mind that whatever you set it to becomes the default when you
use the Quick Sort (A-Z/Z-A) buttons.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[gr43023]

I wasn't aware that pivot tables could be manipulated using the Data > Sort command from the menu bar; I have always used the dialog boxes available through the pivot table. I followed your suggestion and the list is now sorted correctly.

Thanks very much for the help!

 

[CyberTaz]

No Problem - glad it was useful! And thanks for the confirmation.

Regards |:>)
Bob Jones
[MVP] Office:Mac