# Change the summary calculation of multiple pivot table fields at once using Macro (Excel)

Pivot table is useful to summary calculation for table. However, there isn’t a simple way to adjust multiple fields to the same function. The traditional way, you can right-click on the pivot table and adjust one-by-one.

Fortunately, there is the easy way to change multiple pivot-fields to the same function, by using the macro-excel. The full reference can be obtained from Moxie (2013). Here is a short macro that converts all the fields in a selected pivot table to the Sum function (code as xlSum) as follow:

`Figure 1: Macro code for adjust multiple fields to the same function (reproduced from Moxie(2013)) (Note: Make sure you have selected the pivot table to change before playing the macro)`
```Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie

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

You can change xlSum with other function such as:  xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. The detail of each function is illustrated in Table 1.

`Table  1: Detail of XlConsolidationFunction enumeration (MSDM Microsoft, 2014)`
 Member name Description xlAverage Average. xlCount Count. xlCountNums Count numerical values only. xlMax Maximum. xlMin Minimum. xlProduct Multiply. xlStDev Standard deviation, based on a sample. xlStDevP Standard deviation, based on the whole population. xlSum Sum. xlVar Variation, based on a sample. xlVarP Variation, based on the whole population. xlUnknown No subtotal function specified. xlDistinctCount Count using Distinct Count analysis.

References:

1. Moxie, D. (2013). How to change multiple pivot table fields to Sum Function – Excel Pivots. [online] Excel Pivots. Available at: http://excelpivots.com/excel/change-multiple-pivot-table-fields-sum-function/ [Accessed 30 Nov. 2014].
2. MSDM Microsoft (2014). XlConsolidationFunction enumeration (Microsoft.Office.Interop.Excel). [online] Available at: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlconsolidationfunction.aspx [Accessed 30 Nov. 2014].