Microsoft Excel —

Report Percentages

In addition to the available choices, such as Sum, Min, Max, and Average, there is another set of pivot table options called the calculation options. These allow you to show a particular field as a percentage of the total, a percentage of the row, a percentage of the column, or as the percent difference from the previous or next item. All these settings are controlled through the .Calculation property of the page field.

The valid properties for .Calculation are xlPercentOf, xlPercentOfColumn, xlPercentOfRow, xlPercentOfTotal, xlRunningTotal, xlPercentDifferenceFrom, xlDifferenceFrom, xlIndex, and xlNoAdditionalCalculation. Each has its own unique set of rules. Some require that you specify a BaseField, and others require that you specify both a BaseField and a BaseItem. The following sections have some specific examples.


Percentage of Total

To get the percentage of the total, specify xlPercentOfTotal as the Calculation property for the page field:

' Set up a percentage of total
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Caption = "PctOfTotal"
    .Function = xlSum
    .Position = 2
    .NumberFormat = "#0.0%"
    .Calculation = xlPercentOfTotal
End With