[Excel VBA] How to Change PivotTable Calculation Methods and Number Formats

In Excel PivotTables, numeric fields are automatically summed by default. However, in many business scenarios, you need to calculate averages, counts, or distinct counts instead of sums. Additionally, applying proper number formatting (such as currency or percentages) is essential for creating professional reports.

This article explains how to use VBA to programmatically change the calculation function and the display format of value fields in a PivotTable.

Complete VBA Code

The following macro performs three key operations on a specific value field (“SalesAmount”):

1. Changes the calculation method from Sum to Average.
2. Sets the number format to Currency (e.g., $1,234.00).
3. Renames the field header to a custom name.

Sub CustomizePivotDataField()

    ' Declare variables
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim ws As Worksheet
    
    ' Set the target worksheet (Change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Error handling in case the PivotTable or Field does not exist
    On Error Resume Next
    
    ' 1. Set the PivotTable (Targeting the first PivotTable on the sheet)
    Set pvtTable = ws.PivotTables(1)
    
    If pvtTable Is Nothing Then
        MsgBox "No PivotTable found on this sheet.", vbCritical
        Exit Sub
    End If
    
    ' 2. Target the specific Data Field (Value Field)
    ' Note: Use the original Source Name from the data source
    Set pvtField = pvtTable.PivotFields("SalesAmount")
    
    If pvtField Is Nothing Then
        MsgBox "The field 'SalesAmount' was not found.", vbCritical
        Exit Sub
    End If
    
    On Error GoTo 0 ' Reset error handling
    
    ' 3. Modify the properties of the Value Field
    With pvtField
        ' --- A. Change Calculation Method (Function) ---
        ' xlSum     : Sum (Default for numbers)
        ' xlCount   : Count (Default for text)
        ' xlAverage : Average
        ' xlMax     : Maximum
        ' xlMin     : Minimum
        .Function = xlAverage
        
        ' --- B. Change Number Format ---
        ' You can use any format string available in Excel's "Format Cells" dialog
        ' Example: "$#,##0.00" for Currency, "0.0%" for Percentage
        .NumberFormat = "$#,##0.00"
        
        ' --- C. Change Custom Name (Header) ---
        ' This sets the label displayed in the PivotTable header.
        ' IMPORTANT: The name cannot be the same as the source column name.
        .Name = "Average Sales ($)"
        
        ' (Optional) Set the orientation to DataField if it isn't already
        .Orientation = xlDataField
    End With
    
    MsgBox "The PivotTable field has been updated successfully.", vbInformation

End Sub

Detailed Explanation of Properties

1. .Function Property

This property determines how the data is aggregated. By changing this constant, you can switch between different statistical calculations.

• xlSum: Calculates the total sum of the values. This is the default behavior for numeric data.
• xlCount: Counts the number of records. This is the default behavior for text data or non-numeric fields.
• xlAverage: Calculates the arithmetic mean.
• xlMax: Displays the largest value in the group.
• xlMin: Displays the smallest value in the group.
• xlDistinctCount: Counts unique values. (Note: This is only available if the PivotTable is added to the Data Model).

2. .NumberFormat Property

This property controls how the numbers appear in the PivotTable. It accepts the same string format codes used in standard Excel cell formatting. Using this property is better than formatting the cells directly because the format persists even if the PivotTable layout changes or refreshes.

Common Examples:

• Currency (with commas): "$#,##0"
• Decimal (2 places): "0.00"
• Percentage: "0.0%"
• Date: "yyyy-mm-dd"

3. .Name Property

This property sets the display name (caption) of the field in the PivotTable header.

Important Constraint: You cannot rename a PivotField to exactly match the Source Name (the column header in your original data).

• Bad: Source is “Sales”, .Name = "Sales" -> Error
• Good: Source is “Sales”, .Name = "Total Sales" -> OK
• Good: Source is “Sales”, .Name = "Sales " (with a space at the end) -> OK (Common workaround)

Summary

To fully customize the values in your PivotTable using VBA:

1. Identify the correct PivotField within the PivotTable.
2. Set the .Function property to the desired aggregation type (e.g., xlAverage).
3. Apply a professional look using the .NumberFormat property.
4. Give it a clear, descriptive header using the .Name property.

By automating these settings, you ensure that every report generated has consistent calculations and formatting, eliminating manual errors.