K2 Tech Update | Tech Tip
Excel :: New PivotTable Options
Significant additions to Excel 2010 include the ability to change how percentage calculations display in PivotTables. More specifically, among other new options users now have the options of showing data as a percentage of the parent row total, percentage of the parent column total, and percentage of parent total. To illustrate, consider the example presented in Figure 1; it shows percentages for each item computed as a percentage of that item relative to the total for the group to which the item belongs. Further, the total for each item group is expressed as a percentage of that total relative to the total of all item groups. This view is known as Percent of Parent Row Total and is accessible from the Show values as options in Excel 2010
Figure 1 - Excel 2010's Percent of Parent Row Total in a PivotTable
The PivotTable shown in Figure 2 calculates percentages based on the relative amount of an item compared to the total for that item's group. The totals for each group are not expressed as relative totals compared to all groups. This view is known as Percent of Parent Total
Figure 2 - Excel 2010's Percent of Parent Total in a PivotTable
Lastly, the PivotTable shown in Figure 3 shows the percentages for each column of each item calculated as that item, relative to the total for all columns of data for that item. Likewise, the total for each item group is expressed as the relative percentage of the value for that group relative to the total of all columns of data for that group. This view is known as Percent of Parent Column Total
Figure 3 - Excel 2010's Percent of Parent Column Total in a PivotTable
If you are a PivotTable user, as you move to Excel 2010, check out these and other new PivotTable options
For a video presentation of this tip, visit www.tinyurl.com/k2tips44.