Last week, Chandoo interviewed me for his Online Excel VBA School, and we talked about using VBA with Excel Pivot Tables.
If you drop a field into the Values area, and that field contains blank cells or text, it’s added as “Count of”, instead of “Sum of”.
With a macro, you can quickly change all the fields from Count to Sum, instead of fixing each pivot table summary function manually.
Select a Summary Function
One way to let users change the function is to create a drop down list of functions on the worksheet. Then, event code runs when the cell changes, and the selected function is shown in the pivot table.
The cell with the drop down list is named FuncSel, as you can see in the NameBox in the screen shot above.
On another sheet, that could be hidden from the users, there is a list of functions, and a formula that looks up the numeric value for each function. The cell with the formula is named FuncSelCode.
How It Works
When the FuncSel cell is changed, the Worksheet_Change code on that sheet runs.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Me.Range("FuncSel").Address Then ChangeAllData (wksLists.Range("FuncSelCode").Value) End If End Sub
The ChangeAllData procedure runs, using the numeric value in the FuncSelCode cell, and changes all the data fields in the pivot table.
Sub ChangeAllData(lFn As Long) 'changes data fields to selected function On Error GoTo errHandler Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Application.ScreenUpdating = False Set pt = wksPTSales.PivotTables(1) On Error GoTo errHandler pt.ManualUpdate = True For Each pf In pt.DataFields pf.Function = lFn Next pf pt.ManualUpdate = False exitHandler: Set pf = Nothing Set pt = Nothing Application.ScreenUpdating = True Exit Sub errHandler: GoTo exitHandler End Sub
Download the Sample File
To see the code, and test it with the sample data, you can download the Pivot Table Summary Function Change workbook.
The file is in Excel 2007 format, and zipped. Enable macros when you open the file.
______________
Hi Debra
This isn’t Excel – this is magic! Thank you for this (and the other great stuff you post)
Best regards
Gerald Strever
Very good tip, indeed.
Is it possible to change the count function to a more standard sum function at the creation of the PT ?
Thanks for your always very helpful blog.
With best regards
Philippe
Perhaps a improvement to include : the modification of the field name depending the selected function …
It’s indeed a bit strange to read “Sum of dollars” as field name when the function “Count” is selected.
Best regards
Philippe
Thanks Gerald! Glad you like it.
@Philippe, if you are creating the pivot table with VBA, you can add code to change all the data fields to Sum.
@Philippe, the headings should change automatically when the function changes. Perhaps there is a problem with the headings if a non-English version of Excel is used.
When I right click on a cell in a pivot I get this I wonder why 🙂
http://tinypic.com/r/dmxysp/7
I think that this is fantastic, however, I was wondering if there is a way to further enhance this by putting in multiple criteria, for example, include Top 10, and/or month.
I knew that there should be a way doing this by VBA… Thanks for sharing!
As a non-VBA user, I have to do it by filling all the “gap” with 0 before I create the Pivot Table. Luckily by using Go to Special – Blank -> 0 -> Ctrl Enter, it can be done within seconds. 🙂