Mass Change of Pivot "Values" from Sum to Average

[mcgonma]

I have about 65 items in the Values section of my pivot table. They are all calculating "Sum of" but I need them to calculate "Average".
Is there a way to do this in a mass batch or do I have to change each and every item?

 

[arun_eagertolearn]

In the Field List on the right hand side, the values would be showing of Sum of....
Please click on arrow next to that, --->Field Settings---->beneath summarize value field by- change sum to average

(In case the field list is not visible, please right click on the pivot table, then click Show field list)

 

[mcgonma]

In the Field List on the right hand side, the values would be showing of Sum of....
Please click on arrow next to that, --->Field Settings---->beneath summarize value field by- change sum to average

(In case the field list is not visible, please right click on the pivot table, then click Show field list)

My question is do i have to do each one individually or is there a way to change them all as a group?

 

[RoryA]

Select a cell somewhere in the pivot table, then run this code:

Public Sub SetDataFieldsToAverage()
   With Selection.PivotTable
      .ManualUpdate = True
      Dim ptf As PivotField
      For Each ptf In .DataFields
         ptf.Function = xlAverage
      Next ptf
      .ManualUpdate = False
   End With
End Sub

 

[RoryA]

Also, it sounds like your source data is probably not well set up for a pivot table.