In Pivot table, How to set up default as "Average of values" instead of SUM or Count of values

[azhufo]

Hi All:

Do you know how to set up DEFAULT as "Average of values" in Pivot table rather than changing the field setting one by one from "Count of Values" to "Avg of values"? I have many columns in the Values Box. Do you think if the only way is VBA coding. Thanks!

 

[RoryA]

There is no way to change the defaults. You have to use code to reformat them afterwards - for example:

Public Sub SetDataFieldsToAverage()

   Dim ptf                         As Excel.PivotField
   With Selection.PivotTable
      .ManualUpdate = True
      For Each ptf In .DataFields
         With ptf
            .Function = xlAverage
            .NumberFormat = "#,##0"
         End With
      Next ptf
      .ManualUpdate = False
   End With
End Sub

 

[azhufo]

Thank you! It works!!!!