Back
Excel

Calling all PivotTable users: Let us know how you like to summarize your data…

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 …


(Click to enlarge)

…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.


(Click to enlarge)

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.

  1. Have you ever used this feature in PivotTables?
  2. If not, why not?  (Might you use it now that you know about it?)
  3. If so, what are the most common options that you choose, and what type of data do you use them with?
  4. 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.