Removing "Sum of" from pivot tables

[Russ]

In 2003, we had a formatting option that would globally remove “Sum Of†from
the pivot table values. I like that feature. However, I can’t seem to find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks

 

[Debra Dalgleish]

I don't know of any option in Excel 2003 that removes the 'Sum Of' from
data fields. Maybe you had a macro that changed the captions, and it was
lost when you upgraded to 2007.

You could create another macro, to run in Excel 2007.

 

[Russ]

The auto format in Excel 2003 would enable the functionality I'm currently
pursuing in 2007. It also enabled the capability to go between a classic
format and the provided auto formats. No macros required.

 

[Russ]

Hi, Just thought I owed you a better explanation of what I'm pursuing.

The Pivot table autoformat functionality in Excel 2003 would do what I'm
discussing. For example, the autoformat would display "Expenses" versus "Sum
of Expenses". There was 20 autoformats available in addition to Classic and
None.

Thanks.

 

[Debra Dalgleish]

Thanks for the detail on what you did in Excel 2003.

The AutoFormat command had some features that weren't accessible through
other methods, like changing the captions to the same as the source
name, and altering the heading rows.

AutoFormat has been replaced by PivotTable Styles in Excel 2007, and
they don't make the same kinds of changes to the pivot table structure.

If you can run a macro, you could use something like this:

Sub ChangeDataCaptions()
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.DataFields
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = pf.SourceName & " "
End If
Next pf

End Sub