Pivot Table Data Ordering

I know I am probably doing something wrong and it is a simple case of ticking a box but I have data which is in my Pivot Table Rows area. However, when it appears on the Pivot Table it has changed order to alphabetical but I need it to stay in the same order as the data table.

This is the order in Data Table (that I want to have in the Pivot Table also):
Eastbourne
Richmond
Malvern
Exeter
Cheltenham
Birmingham
Manchester
Inverness
Glasgow
Edinburgh


This is the order in Pivot Table (which I don't want):
Birmingham
Cheltenham
Eastbourne
Edinburgh
Exeter
Glasgow
Inverness
Malvern
Manchester
Richmond

Am I doing something wrong???

nikwak said:

I know I am probably doing something wrong and it is a simple case of ticking a box

Click to expand...

Unfortunately, it is not a case of ticking a box!
You have to use Custom Lists.
The source data you say has stuff in the correct order; you need to create a custom list from that column of towns in the source data. This process can be quite quick. See the first section of http://excelribbon.tips.net/T006243_Importing_Custom_Lists.html to do this.

Now that you have a custom list, you need only to sort A-Z using the dropdown in the pivot table. If this doesn't immediately work, you need to check a setting - yes, this time it IS just a checkbox: right-click the Pivot table and choose Pivot table options…, in the Totals & Filters tab, in the Sorting section, make sure there is a tick in the Use Custom lists when sorting tick box.

Come back if this doesn't work for you.

Thank you - that has done it. And thank you for explaining it so clearly. It is very much appreciated