View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Debra Dalgleish Debra Dalgleish is offline
external usenet poster
 
Posts: 2,979
Default Removing "Sum of" from pivot tables

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


Russ wrote:
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" wrote:


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 wrote:

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

Does anyone have solution to this question?

Thanks



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com