Problem: Excel offers eleven functions on the Summarize Value By tab of the Value Field Settings dialog (back in Fig 916). Those are not the good ones. The good ones are on the Show Values As tab. Ninety percent of the time that I change the calculation, I am using one of the relatively hidden Show Values As calculations.
Strategy: Excel 2010 moved these to the Show Values As dropdown so people might discover them. The really good calculations are here.
- Show values as a variety of calculations.
Gotcha: Several of those calculations are new in Excel 2010 and will not calculate in Excel 2007: Both of the Ranks, all of the Parent calculation, and the % Running Total In.
To create the pivot table at the top of the figure above, add Revenue five times to the Values area of a pivot table. Select a cell in each column. In Excel 2010, use the Show Values As dropdown. In Excel 2007, use Field Settings, then click Show Values As.
Gotcha: The calculations require one, two, or zero arguments. An example of each follows.
The numbers in C4:C10 use the % of Column Total setting. You simply choose this setting. You don't have to specify any additional information.
Rank and Running Total are examples where Excel will ask you to identify the base field. Most often, this will be the row field.
% Difference From is a calculation that requires a Base Field and a Base Item. The calculation in F4:F9 expresses revenue as a percentage of Manufacturing revenue.
-
Enter a base field and a base item.
The (previous) entry in Fig 919 is great for reports with dates. This report show the sales as a percentage change from the previous day.
- Change from previous day.
Additional Details: You can combine the 11 functions on Summarize Values By and the 15 settings under Show Values As. The figure above is showing the average sale for each day and then the % change from the previous day of the average sale.
Percentage of Parent Row Total and the other "Parent" calculations are new in Excel 2010. This calculation was very difficult before Excel 2010. In this figure, the percentages in C16:C18 express the revenue as a percentage of the total sector revenue in B15.
The confusing part is that the 14.35% in C15 shows how the Communications sector total of $962K compares to the grand total of $6.7 million. All of the percentages are correct, it is just strange to see a smaller number on the total line than on the detail lines.
