Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas 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:
————————————————
To manage calculations which are beyond ‘Summary Functions’ and ‘Custom Calculations’, you can create your own formulas by inserting ‘Calculated Fields’ or ‘Calculated Items’ in a PivotTable report.
CalculatedFields.Add Method: Use the CalculatedFields.Add Method to create a calculated field in a PivotTable report. It has 3 arguments – Name, Formula & UseStandardFormula, wherein Name & Formula arguments are mandatory to specify.
Example 1: Using the CalculatedFields.Add Method to create a calculated field. Refer Image 1a which shows a PivotTable report named “PivotTable1” showing Sales using the summary function of Sum. Image 1b shows the Pivot report with a new calculated field added “Variance-%”, using the below code. Note that the source range field names are “Sales” and “Budgeted Sales” on which calculations are based.
Sub PivotTableCalculatedFields1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘for empty cells in the data area of a PivotTable report, show a specified value, using the PivotTable.NullString Property:
PvtTbl.NullString = “0”
PvtTbl.DisplayNullString = True
PvtTbl.CalculatedFields.Add Name:=“Variance”, Formula:=“=IF(OR(Sales=0,Budgeted Sales=0),0,(Sales – Budgeted Sales)/Budgeted Sales)”
With PvtTbl.PivotFields(“Variance”)
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = “0.00%”
.Caption = “Variance-%”
End With
End Sub
Example 2: Use the PivotTable.CalculatedFields Method to return calculated fields in a PivotTable.
Sub PivotTableCalculatedFields2()
Dim PvtTbl As PivotTable
Dim fld As PivotField
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each fld In PvtTbl.CalculatedFields
MsgBox fld.Name
Next
End Sub
CalculatedItems.Add Method: Use the CalculatedItems.Add Method to create a calculated item in a PivotTable report. It has 3 arguments – Name, Formula & UseStandardFormula, wherein Name & Formula arguments are mandatory to specify.
Example 3: Using the CalculatedItems.Add Method to create a calculated 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 a new calculated item added “Europe/N America”, using the below code.
Sub PivotTableCalculatedItems1()
Dim PvtTbl As PivotTable
Dim rng As Range
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“Region”).CalculatedItems.Add Name:=“E/N Am”, Formula:=“=Europe/North America”
With PvtTbl.PivotFields(“Region”).PivotItems(“E/N Am”)
.Position = 3
.Caption = “Europe/N America”
End With
‘use the PivotItem.DataRange Property to set number format in the values area separately to show Sales in $ and show calculated item values as percent:
PvtTbl.PivotFields(“Region”).PivotItems(“Europe”).
DataRange.NumberFormat = “$#,##0”
PvtTbl.PivotFields(“Region”).PivotItems(“North America”).DataRange.NumberFormat = “$#,##0”
PvtTbl.PivotFields(“Region”).PivotItems(“Europe/N America”).DataRange.NumberFormat = “0.00%”
End Sub
Example 4: Use the PivotField.CalculatedItems Method to return calculated items in a PivotField.
Sub PivotTableCalculatedItems2()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“Region”).CalculatedItems
MsgBox pvtItm.Name
Next
End Sub
Example 5: To get a List of all formulas (calculated items and fields) used in a PivotTable, use the PivotTable.ListFormulas Method. This opens a new worksheet, displaying a list of all formulas used.
Sub PivotTableCalculatedFieldsItems()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.ListFormulas
End Sub