Pivot table sorting (Excel 97)

I have a pivot table with two field rows and a total column. I would like to sort the total column so that it is in descending count order of field B for each change in field A. When I click on the total column and sort it, it only sorts correctly for the first field A entry. The remainder of field A changes are not sorted.

I've attached a simple example - in the pivot table I want it sorted so that the total column count is descending by Fruits and descending by Vegetables.

I'm pretty sure I was able to do this one time, but just can't seem to find a way to do it now. Can anyone help? Thanks.

Re: Pivot table sorting (Excel 97)

If you double click on the Food Field heading in your Pivot Table, you should launch a dialog for setting some options. Click on advanced and you should be able to set th eAutoSort option to descending.

Re: Pivot table sorting (Excel 97)

Sorry Kevin,

I omitted to include that when you set the sort order to descending, you should set the Using Field Dropdown list to Count of Category . You might also want to set the same options for the Category field so that the category that contains the food item with the highest count comes first.