Assuming I have a pivot table such as

Category Year Amount
A 2009 100
A 2010 150
A 2011 120
Total A: 370
B 2009 50
B 2010 60
B 2010 70
Total B: 180
C 2009 100
C 2010 180
C 2011 50
Total C: 320
Grand Total 870

How would I add totals for each year to the pivot table? I don't want to convert to values and manually insert a couple of manually calculated rows, as the table will need to be able to automatically recalculate. (Amount is several different column fields, simplifed for the example)

e.g. the last rows of the above table are:

Total C: 320
    Total 2009 250
    Total 2010 390
    Total 2011 220
Gramd total 870

The results of the cross-tab query are currently being displayed in a pivot table; the problem with the pivot table is that you can only sub-total; Excel only produces the first output.

If "Year" was a column field, then the grand totals for those rows would be the numbers i would be interested in showing. If "Year" was the outer-most field, then the sub-totals would also show the numbers i would want.


Did you try to build a pivot table ?

| improve this answer | |
  • Yes, but i'm not sure how to build both the sub total, and the year based grand total ; with no sub-categories, enabling "subtotal year" does nothing (enabling sub-total for category adds a new line with the total of both years on it) – Andrew Hill Aug 8 '11 at 0:43
  • did you try grouping data ? see: contextures.com/xlpivot07.html and ozgrid.com/Excel/grouping-pivot-tables.htm – JMax Aug 8 '11 at 6:25
  • The problem is not related to grouping data; if i was to move the year row group to the column set, the row grand total would contain the numbers i want to see; i'm just not sure how to actually to get those grand tota-by-year numbers to show when year isn't the top level group. – Andrew Hill Aug 9 '11 at 4:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.