We help IT Professionals succeed at work.

Prefixes OFF

suobs asked
I would like to produce Pivot Tables and Pivot charts without the "Sum of", "Count of" information that is inserted in front of variable names in column headers and Chart categoris.  

Is there a way to turn this thing OFF?
Watch Question

You can reasonably adjust these titles while you are creating the pivot table.

In the layout of the Pivot Table Wizard, let's say you have a field called Revenue and that field is in the Data section of the pivot table.

Double click that field and you will get the PivotTable Field dialog. The default name of the field will be "Sum of Revenue".  You can change this, but not back to Revenue, as that is already a name in the table.  You can, however, switch it to something like "Revenue Total" or something less geeky than "Sum of Revenue".

Does this solve the problem?  

Another choice would be a macro that could run and change the captions from "Sum of Amount" to "Amount Total".

Sub ChangeSumOf()
  For each PT in ActiveSheet.PivotTables
    For Each PF in PT.DataFields
      OldVal = PF.Caption
      If Left(OldVal, 6) = "Sum of" then
        NewVal = Mid(OldVal, 8, Len(OldVal) - 7) & " Total"
        PF.Caption = NewVal
      End if
    Next PF
  Next PT
End Sub
suobs: Could we get some feedback here? (And in your open question in the Access topic area?)

We're cleaning up this topic area and need all the help we can get.

Community Support Moderator


Thanks!  Sorry for spacing it out . . .
No problem, suobs. Thanks for responding!

community support moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.