Change Selected Pivot Fields to SUM

When you create a pivot table, Excel automatically makes the value fields either a SUM or a COUNT, depending on the type of data in that field. You’re not stuck with that function though — if you don’t like the summary function that Excel selected, you can change it.

It’s quick and easy to change one field manually, but if there are lots of fields to change, programming is a better option. Previously, I’ve posted code that lets you change all the value fields to SUM.

Today, we’ll look at some code that changes only the selected fields. This will be useful if you want to leave some fields as COUNT, and change the rest to SUM.

Change Selected Fields Only

In the pivot table shown below, all three value fields are summarized by the COUNT function. I’d like to leave the OrderDate field showing the count, and change the other two fields to show a SUM.

So, I selected a value in each of those fields – Quantity and TotalPrice. I could have selected more than one value in each field, but the code will check each selected cell, so that would slow things down.

Code: Change Selected Pivot Fields to SUM

You can find the following code in my sample file, or copy it from here.

1. First, the code checks to see if the active cell is in a pivot table.
2. Then, for each cell that is selected, it tries to change that pivot field’s function to SUM.

Sub SumSelValueFields()
Dim pt As PivotTable
Dim c As Range
'can be slow -- ManualUpdate not turned on

   On Error Resume Next
   Set pt = ActiveCell.PivotTable
   
   If pt Is Nothing Then
      MsgBox "Please select a value cell in the pivot table"
   Else
      Application.ScreenUpdating = False
      For Each c In Selection
         c.PivotField.Function = xlSum
      Next c
   End If
  
exitHandler:
  Application.ScreenUpdating = True
  Set pt = Nothing
  Exit Sub
   
errHandler:
  GoTo exitHandler

End Sub

Manual Updating Not Turned On

In most pivot table macros, I set the pivot table’s ManualUpdate property to TRUE, so the code runs faster. It’s not changed in this code, because Excel loses track of the field names, and doesn’t update the field functions correctly. So, if you use this code in a large pivot table, it might run a bit slowly.

Pivot Power Add-in

Another option, which I use in my Pivot Power add-in, is to collect the field names in an array, at the start of the macro, then use those names to update the fields. With this technique, I can set the Manual Update property to TRUE, so it runs faster.

If you’ve bought a copy of my Pivot Power Premium add-in, this feature is included. Just click the commands in the Data Field Functions group, and you can quickly change all fields to SUM, or any other summary function. Or, select specific value fields, and just change those to a different summary function.

Download the Sample File

To see the workbook and the SUM selected fields code, you can download the sample file from my Contextures website. On the Summary Functions page, go to the Download section, and look for the RegionSalesMacros file.

______________