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.