Tag Archives: statistic

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].