User Rating: 5 / 5

Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA

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:

Pivot Table Calculated Fields

Pivot Table Calculated Items

------------------------------------------------

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.

Pivot Table Calculated Fields

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

Pivot Table Calculated Items

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")

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