Sorting a sub category in an Excel 2007 pivot table

[Rudi]

Hi,

If I have a pivot with the primary field in the row category as Make (of car) and a second field called Model, the resulting pivot will show groups like Opel, Toyota, BMW, ETC. In the sub group of Toyota will be Corolla, Tazz, Camry, etc.

My question is how do I sort only the sub data (Models) of the Toyota group without sorting the models in BMW or Opel, etc. (The default behaviour is that it sorts the Models asc. or desc. in ALL the Make groups, even if I only select the Toyota models.) I only want the sort in the "Toyota" group.

Hope this is clear.

[HansV]

I don't think you can do that in a pivot table. It handles the subcategories the same way for each category.

[Rudi]

TX. I just needed to bounce that off an expert to confirm my suspicions.

Cheers

[Rudi]

Hi,

Another feature that I have noticed cannot be done in a pivot chart is to include totals. If I have a basic pivot table with a row label field and a column label field and a values field, the resulting chart plots well, but one cannot tweak it to include the totals.

PS: I know its not the right thing to do in a chart, as it skews the chart with the large totals column, but I had a query the other day where a person needed this included. I discovered then that it cannot be added. What I did was to copy the pivot table and paste it on another sheet, and then drew up a standard column chart including the totals.

[HansV]

This is by design, and quite rightly so. As you note yourself, it's not correct to include the totals in the chart.

The stacked column/bar/line chart types are the ones to use if you want to be able to see both the individual data and their total.

[rory]

Regarding the sorting, you can manually rearrange the items in the Toyota category, or you could use a custom list to determine the sort order you want.

[Rudi]

Hi Rory,

Unless I am missing something, both your suggestions still re-arrange the whole field through all the makes of car. I tried manual re-arranging and I set up a custom list and used that in my custom sort. Neither sorted only the models in the Toyota make...??

[rory]

I don't see how dragging and dropping a Toyota model could affect any other make?

[Rudi]

Aah Ha, here is the culprit!

When I tested your theory, I was using a different sample pivot, where I had a few duplicate values in the different categories. When I dragged a duplicate value, it changed the position of the duplicates in the other categories too. It is only when you drag a unique value that you can arrange it in any order.

Conclusion:
Rory, you are correct...in a sample car table where there should be no duplicate models in each category, this is quite possible. If the data is of a nature where the sub category is duplicated to any degree, then the who sub field re-arranges through all the main categories.

TX Rory