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.


  1. Moxie, D. (2013). How to change multiple pivot table fields to Sum Function – Excel Pivots. [online] Excel Pivots. Available at: [Accessed 30 Nov. 2014].
  2. MSDM Microsoft (2014). XlConsolidationFunction enumeration (Microsoft.Office.Interop.Excel). [online] Available at: [Accessed 30 Nov. 2014].

About zulkarnainh

I was born in Melaka (1987), holds the degree in B.Eng.(2010), M.Eng (2012) and Ph.D. (2016) in Civil Engineering at Universiti Teknologi Malaysia. I have been the UniMAP since 2016 and currently serving as Senior Lecturer. Feel free to contact me if you are interested to collaborate or pursuit a study (Master or Ph.D.) with me. Thank you!
This entry was posted in Code and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *