Changing PivotField Summary Stats 1

[heavnstrl]

I use pivot tables in Excel XP extensively. When creating the pivot table through the wizard, excel 'automatically' determines how to summarize the data (either SUM or COUNT). I frequently use AVERAGE and have yet to find a way to set a default summarization type. Has anyone found a way to override the default or at least a way to batch change pivot fields (excel is requireing me to manually change them one at a time) from xlSum to xlAverage?

Thanks, Carl

 

[SkipVought]

Hi, heaven,

Don't know of a way to set a default. Maybe some guru out there can come up with it.

However, here's a way to run a macro after the fact, to change every data field to average...

Sub SetDataFieldsAverage()
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Age").Function = _
        xlAverage
    For Each PivotTable In ActiveSheet.PivotTables
        For Each DataField In PivotTable.DataFields
            DataField.Function = xlAverage
        Next
    Next
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[heavnstrl]

Hi Skip,

This worked perfectly. I hadn't realized that I could loop through each datafield with the For Each call. My previous macros were explicitly stating the datafield name which could get pretty tedious.

Thanks!

Carl