Changing default for field settings in pivot tables

**jim55anderson** · 12-02-2009, 12:04 PM

Every time I create a pivot table, the fields are automatically set to Sum. Is there a way to change the default so I can get them to automatically be set to Average as I build the table. I have a ton of fields to add and do not want to have to change the field settings on every one.

**romperstomper** · 12-02-2009, 12:08 PM

Unfortunately not, but you can use a macro to apply that afterwards:

Code:

Public Sub SetDataFieldsToAverage()
'
' Sets all data fields to average
   Dim ptf As 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

**jim55anderson** · 12-02-2009, 12:15 PM

Thanks RomperStomper,

I was afraid that was going to be the answer. By the way, do you have similar code that sets the defaults to not display sub-totals?

Thanks Again,
Jim

**romperstomper** · 12-02-2009, 12:19 PM

Yep.

Code:

Sub RemovePivotSubtotals()
   Dim PT As PivotTable
   Dim pf As PivotField
   On Error Resume Next
   Set PT = ActiveCell.PivotTable
   If PT Is Nothing Then Exit Sub
   PT.ManualUpdate = True
   For Each pf In PT.RowFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
      For Each pf In PT.ColumnFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
   PT.ManualUpdate = False
End Sub