Pivot Table - Remove "sum of" in data field

Is there a way to modify Pivot Table so that "Sum of, Count of", etc is not shown.
For example, have Table (& Pivot Chart) show "West Region" instead of "Sum of West Region"

Thanks

Hi

If you right-click on the pivot table and select "Field Settings", you can change the text that is displayed by amending the "Name" field.

Hope this helps!

Best regards

Richard

That worked. Thanks very much

the following code will achieve what you want.

Sub ChangePTName() 
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long 
Application.ScreenUpdating = False 
Set ws = ActiveSheet 
For i = 1 To ws.PivotTables.Count 
Set pt = ws.PivotTables(i) 
pt.ManualUpdate = True 
For Each pf In pt.DataFields 
If pf.Function = xlSum Then 
If Left(pf.Caption, 6) = "Sum of" Then 
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6) 
End If 
End If 
Next 
pt.ManualUpdate = False 
Next i 
Application.ScreenUpdating = True 
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

This is awesome. Thank you!

Originally Posted by talhawahab

the following code will achieve what you want.

Sub ChangePTName() 
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long 
Application.ScreenUpdating = False 
Set ws = ActiveSheet 
For i = 1 To ws.PivotTables.Count 
Set pt = ws.PivotTables(i) 
pt.ManualUpdate = True 
For Each pf In pt.DataFields 
If pf.Function = xlSum Then 
If Left(pf.Caption, 6) = "Sum of" Then 
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6) 
End If 
End If 
Next 
pt.ManualUpdate = False 
Next i 
Application.ScreenUpdating = True 
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

Originally Posted by TexExp

+ 1,000! Thanks


You will also have to save your file as a 'macro enabled workbook'.

How can this thing be a default for all Excel Workbook?

Originally Posted by pumbaa

This is awesome. Thank you!

This works great with a regular Pivot Table but doesn't work with pivot table created using PowerPivot. Any solution?

Thank you!


Joji

FANTASTIC! Thanks Tal!

Thanks so much!

This has worked perfectly for me and saved me a lot of frustration. Appreciate the simple instructions as well.

Many Thanks

Just used this - really big help - thank you

Is there a version/language independent workaround? For example in dutch "sum of" becomes "Som van". Since i want my macro not to be depended on the language excel is installed, the code above wont work.

This part wont work:

If Left(pf.Caption, 6) = "Sum of" Then

Thanks that did the trick!

Thank you so much, great help!

works, thanks!

Another quick workaround is to replace "Sum of" with a blank space.. or delete it out and put a space after the variable name. If you have a lot of variables you can just Find/Replace "Sum of" with nothing and that will give you the same result.