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)
|xlCountNums||Count numerical values only.|
|xlStDev||Standard deviation, based on a sample.|
|xlStDevP||Standard deviation, based on the whole population.|
|xlVar||Variation, based on a sample.|
|xlVarP||Variation, based on the whole population.|
|xlUnknown||No subtotal function specified.|
|xlDistinctCount||Count using Distinct Count analysis.|
- 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].
- 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].