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 Admin