A lull in the action this week gives me time to talk about Excel 2010. So I’ve made this week, Excel 2010 Improvements week.
Every day this week, I’ll take time to blog about some of the new features in Excel 2010 that strike me as real improvements.
Today’s topic is the new ‘Percent of PivotTable SubTotals’ function in Excel 2010.
The Problem:
In Excel 2007, you can use the ‘Show Values As’ function to show your pivot table data in through the prism of various types of analyses.
For example, you can show values as the % of Column. This view gives you the percent distribution across the entire sample set.
Here, you see that the Dallas market makes up 4.96% of the sales of the complete dataset.
This is fine and dandy, but what this doesn’t show is what percent of the South region’s revenue is made up of Dallas sales.
The Excel 2010 Fix:
Showing the percent of SubTotal has been on the wish list for many analysts for such a long time.
Just ask Debra Dalgleish(Contextures.com) who seems to be the designated MVP to answer these questions.
Well, it seems Microsoft has finally conceded, adding this ability in Excel 2010!
In Excel 2010, you can right-click on the values and select Show Values As –> % of Parent Row Total.
The result will be a pivot table that shows the percent distribution of each market within each region.
That is to say, you can now see that the Dallas market makes up 6.92% of the South total!
This is a fantastic addition to PivotTables!
This alone should make most of you analysts ready to switch to Excel 2010.
Ok…maybe that’s a bit dramatic.
“This alone should make most of you analysts ready to switch to Excel 2007.”
I assume you mean Excel 2010!
Jonas: oops. Thanks for the correction. Fixed it.
i thought that was just a more subdued version of DP humor…like Excel 2010 is so neat that it should draw you 2003 users closer to being current…although maybe not SO great that you should plunge all the way into 2010 quite yet…
Also the fact that you can repeat Item lables….