apply two nested sub-totals to a table in Excel 2007

Question

I would like to apply two nested sub-totals to a table in Excel 2007, but I find that the "SubTotal" button does not seem to work consistently.

Imagine the following table

Year    Colour  Amount
2011    Red     10
2011    Green   20

If I select all the rows and click on "SubTotal" with the following parameters

At each change in - Year
Use Function - Sum
Add subtotal to - Amount
Replace current subtotals - No
Page break between groups - No
Summary below data - No

I then do exactly the same but - At each change in - Colour

I get the following nice table which is exactly what I want

Year    Colour  Amount
Grand Total     30
2011 Total      30
    Red Total   10
2011    Red     10
    Green Total 20
2011    Green   20

Imagine though that I change "Green" to "Red" in the original table

Year    Colour  Amount
2011    Red     10
2011    Red     20

If I follow the above steps, then I would expect to see the table looking in the same format. However I get the following

Year    Colour  Amount
    Red Total   30
Grand Total     30
2011 Total      30
2011    Red     10
2011    Red     20

As you can see the Red Total is at the top whereas I would like it to be after the row "2011 Total" just as it is in the first table.

Is there any way of working around this. [I know I can do this manually, but would like to use the SubTotal button]

To summarize, I need both the 2011 and the Colour to be sub-totaled, but in a way that first the 2011 is sub-totaled, and then the Colour (as in the first table I show above). This needs to work whatever the data that makes up the table. For me, it only works with the first data set, but not the second data set.

QUESTION UPDATED

Here is an example. The example includes an extra ten "Price" columns not mentioned in the problem. The reason why I have shown these ten columns is to show why PivotTables are not my ideal solution. I did not think it is possible with PivotTables to show the "details" of the rows inline with the subtotals - in the manner depicted in the picture.

enter image description here

You should learn how to use PivotTable reports. They do exactly what you need — edeboursetty
Thanks d--b. PivotTables are definitely an option. However what I omitted from the question was that there are another 10 columns which I want to display. Admittedly the Pivottable would allow me to see these columns via "Show Details" however I ideally wanted 1. the ability to see the details inside the actual report itself 2. to do this for multiple drill-downs- something which I think is difficult with Pivottables — gordon613
That's a good point. But at least now I know that I probably have to write code for it and if I ever write the code I can post it here! — gordon613

Stepan1010 Stepan1010 · Accepted Answer · 2012-09-05T14:35:53

I agree with d--b - you are going to want to use pivot tables - they provide alot more intuitive features(in my opinion) than the subtotal system does:

PivotTableExplanation

The subtotal system is dependent on the initial sorting and organization of the data for what it produces. Whereas pivot tables do not care whether/how your initial data is sorted.

Good Luck.

EDIT:

You can still manipulate the subtotal table using standard copy/cut and paste features - it will maintain its tree structure:

SubtotalSolution1 SubtotalSolution2 SubtotalSolution3 SubtotalSolution4

So basically feel free to move the rows around using cut and paste however you want it to look - you can even move individual cells around within a row - it will still maintain it's structure/look. Good Luck.

apply two nested sub-totals to a table in Excel 2007

2 Answers