A few months back I wrote several posts about the improvements that we made to PivotTables in Excel 2007. I even showed how summarizing data in different ways is now just a right click away on the â€œSummarize Data By…â€ menu. What I didnâ€™t show was that in addition to these standard ways of summarizing data (sum, count, max, min, average, etc.), Excel already supports some more complex summary views for items contained in a PivotTable. For instance, by selecting â€œMore Options,â€ you can choose to show the data as a â€œRunning totalâ€ or â€œ% of Total.â€
So, given the data below in a PivotTable …
…you can choose to â€œSummarize Data By… > More Optionsâ€ and then show the values as the â€œ% of totalâ€….
…. and Excel will automatically perform the calculation for you in the PivotTable.
Weâ€™ve gotten a few pieces of feedback about this functionality, usually that (a) itâ€™s useful but hard to find, or (b) it would be more useful if there were additional options for how the data could be shown in addition to the ones listed in the dialog above.
We have someone on the team starting to look at this as part of our planning for future releases (the version after 2007), so weâ€™d like to hear what you think. Here are some specific questions we would love to hear your answers to, but feel free to add additional commentary too.
- Have you ever used this feature in PivotTables?
- If not, why not? (Might you use it now that you know about it?)
- If so, what are the most common options that you choose, and what type of data do you use them with?
- Perhaps most interestingly, are there additional options that you would like to see added (for instance – â€œ% of parentâ€ could show the percentage of an item to its parent group instead of to the column total)?
Please post your answers using comments, as it is easier for everyone on the Excel team to read comments (as opposed to the email link which sends email just to me).
Thanks for your help.