How to remove Sum OF from Microsoft Excel

**Ulrich** · 13-01-2012

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?

**Hollyn** · 13-01-2012

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.

**Shurod** · 13-01-2012

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. 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.

**JyotiT** · 13-01-2012

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:

Code:

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

**BillieJ** · 13-01-2012

There is a common solution which I think can help you. For that just right click on the pivot table and choose Field Settings. In that you can modify the text and display the same as you want by amending the Name field.