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:

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

Please follow and like us: