Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
------------------------------------------------------
Contents:
1. Pivot Table Summary Functions
2. Pivot Table Custom Calculations
------------------------------------------------------
Calculating the Value Fields in a PivotTable report: Calculations are a great way to do data analysis and comparisons in a PivotTable report. For calculating the Value Fields, you can choose any of the "Summary Functions" or use any "Custom Calculations" in the Value Field Settings of a Pivot Table, or create your own formulas by inserting "Calculated Fields" or "Calculated Items".
1. Pivot Table Summary Functions
You can choose the type of calculation that you want to use to summarize the data from the selected field. A PivotTable report created initially from the source data, shows the value field as "Sum of Sales" ('Sum' being the default calculation for a value field with numbers) for the field name of 'Sales'. You can choose calculation options for a value field in the 'Value Field Settings' dialog box in the worksheet which has a list of functions from which you can choose the type of calculation with which you want the Value Field to be summarized. Or you can use VBA for doing this, as shown below.
The PivotField.Function Property sets the functions used to summarize the data fields for a PivotTable report. A total of 11 functions are available, the only function which works with text fields is Count. The 11 functions are: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. Refer section on Excel Pivot Tables: Summary Functions for details on what each function does.
Example 1: Set the Summary Function property of a data field - refer Images 1a to 1e as per below.
The existing data field has the summary function of SUM and its caption (or Custom Name) is "Sum of Sales" - refer Image 1a.
Sub PivotTableSummaryFunctions1b()
'change summary function from xlSum to xlAverage - refer Image 1b:
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Function = xlAverage
.NumberFormat = "#,##0"
.Caption = "Avg Sales"
End With
End Sub
Sub PivotTableSummaryFunctions1c()
'change summary function from xlAverage to xlCount - refer Image 1c:
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Avg Sales")
.Function = xlCount
.NumberFormat = "#,##0"
.Caption = "Sales-Count"
End With
End Sub
Sub PivotTableSummaryFunctions1d()
'change summary function from xlCount to xlMax - refer Image 1d:
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sales-Count")
.Function = xlMax
.NumberFormat = "#,##0"
.Caption = "Sales-Max"
End With
End Sub
Sub PivotTableSummaryFunctions1e()
'change summary function from xlMax to xlStDevP - refer Image 1e:
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sales-Max")
.Function = xlStDevP
.NumberFormat = "#,##0.00"
.Caption = "Sales-StDevP"
End With
End Sub
2. Pivot Table Custom Calculations
Pivot Table Data is summarized by functions like Sum, Count, Average, … and there are different methods (referred to as custom calculations) as to how the Values can be shown in the report. In a custom calculation values are calculated as based on other items. After selecting a function to summarize the value field (viz. Sum, Count, Average, …) you can 'Show values as' per a list of custom calculations viz. Normal (means there is no additional custom calculation), Difference From, % of, % Difference From, …. Custom calculations usually involve selecting a 'Base field' and / or a 'Base item'. You can do this by clicking the 'Show values as' tab in the 'Value Field Settings' dialog box in the worksheet which displays a drop-down list of custom calculations, or by using VBA code as explained below.
We show how to do custom calculations by applying the calculation property to the PivotField - following properties can be used: xlNoAdditionalCalculation, xlDifferenceFrom, xlPercentOf, xlPercentDifferenceFrom, xlRunningTotal, xlPercentOfRow, xlPercentOfColumn, xlPercentOfTotal and xlIndex. These are explained below with the help of examples.
Example 2: Show data field values as Difference From a specific Item.
Refer Image 2 which shows a PivotTable report at the top showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Difference from the Car Model "Compact", using the below code.
Sub PivotTableCustomCalculations2()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Caption = "DiffFrmCompact"
.Position = 1
.Calculation = xlDifferenceFrom
.BaseField = "Car Models"
.BaseItem = "Compact"
End With
End Sub
Example 3: Show data field values as Percentage of a specific Item.
Refer Image 3 which shows a PivotTable report at the top showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Percentage of the Car Model "Compact", using the below code.
Sub PivotTableCustomCalculations3()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00%"
.Caption = "%ofCompactSales"
.Position = 1
.Calculation = xlPercentOf
.BaseField = "Car Models"
.BaseItem = "Compact"
End With
'for error cells in the data area of a PivotTable report, show a specified value, using the PivotTable.ErrorString Property:
With PvtTbl
.ErrorString = "N/A"
.DisplayErrorString = True
End With
'align cells in the data field of a PivotTable report:
PvtTbl.DataBodyRange.HorizontalAlignment = xlRight
End Sub
Example 4: Show data field values as Percentage of Previous Month - companies usually require a month-to-month comparison of sales or profitablility.
Refer Image 4 which shows a PivotTable report on the left showing monthly Sales using the summary function of Sum. The Pivot report on the right shows these Sales values as Percentage of Previous month, using the below code.
Sub PivotTableCustomCalculations4()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet6").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00%"
.Caption = "Sales-%DiffPrev"
.Position = 1
.Calculation = xlPercentDifferenceFrom
.BaseField = "Month"
.BaseItem = "(Previous)"
End With
End Sub
Example 5: Show data field values as Running Total.
Refer Image 5 which shows a PivotTable report on the left showing Sales using the summary function of Sum. The Pivot report on the right shows these Sales values as Running Total In the "City" field, using the below code.
Sub PivotTableCustomCalculations5()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Caption = "RunningTotalCity"
.Position = 1
.Calculation = xlRunningTotal
.BaseField = "City"
End With
End Sub
Example 6: Show data field values as Percentage of Row.
Refer Image 6 which shows a PivotTable report at the top showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Percentage of Row (each country's sales are shown as %age of the region it is in), using the below code.
Sub PivotTableCustomCalculations6()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00%"
.Caption = "Sales-%ofRow"
.Position = 1
.Calculation = xlPercentOfRow
End With
End Sub
Example 7: Show data field values as Percentage of Column.
Refer Image 7 which shows a PivotTable report at the top showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Percentage of Column (compare with the next example of "Percentage of Total"), using the below code.
Sub PivotTableCustomCalculations7()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00%"
.Caption = "Sales-%ofColumn"
.Position = 1
.Calculation = xlPercentOfColumn
End With
End Sub
Example 8: Show data field values as Percentage of Total.
Refer Image 8 which shows a PivotTable report at the top showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Percentage of Total, using the below code.
Sub PivotTableCustomCalculations8()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00%"
.Caption = "Sales-%ofTotal"
.Position = 1
.Calculation = xlPercentOfTotal
End With
End Sub
Example 9: Show data field values as Index.
Refer Image 9 which shows a PivotTable report at the top named "PivotTable1" showing Sales using the summary function of Sum. The Pivot report at the bottom shows these Sales values as Index [index is calculated as: (Cell Value * Grand Total) /(Row Total * Column Total)], using the below code.
Sub PivotTableCustomCalculations9()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet8").PivotTables("PivotTable1")
With PvtTbl.PivotFields("Sum of Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0.0000"
.Caption = "Sales-Index"
.Position = 1
.Calculation = xlIndex
End With
End Sub
