Change Chart Series Collection Name in a Pivot Chart

Pivot charts have many restrictions and limitations. I cannot be sure, but I expect that this is one of them -- the data series name must be linked to the column headings in the pivot table.

What is your end goal in changing the name? Why do you want the series name to be different from the column name in the pivot table? I expect that, in order to do this, you will need to create a normal chart from pivot table data (and do it in a way that makes certain that the chart is not converted by Excel to a pivot chart). See this essay: http://peltiertech.com/regular-chart...-pivot-tables/

I have little expertise with pivot tables and charts. It appears to me that the legend text is linked to the column headers in the pivot table, and the column headers in the pivot table are linked to the text in that field in the source data. My thought would be to edit the source data so that the text in this field is shorter, or add an additional column/field ("short name") and enter the desired abbreviated text in this field, then use this field as the column headers.

It is not possible to have more than one field with the same caption, which is what your code would produce. You also cannot use the name of a pivot field as its caption- it must be different in some way. You might perhaps use something like this

Please Login or Register  to view this content.

which will take everything up to the first space in the current caption, and prefix that with a space purely to make it (hopefully) different from the field name.

It seems those are not spaces. Perhaps this

Please Login or Register  to view this content.

If you are seeing it, and the code changes it at all, it must be part of the caption. Can you provide a sample workbook?

Here's what I tried

1) Select a cell in column G of the pivot table
2) PivotTable Tools -> Field Settings -> Custom Name -> Enter a suitable shorter text string (I used "a" and " " to test. It will not let me put nothing).
3) Not only did this change the text in G5, but it also changed the text in every "Average of COMPLEX_UTIL_PCT" column and data series.

If the main concern is the length of the text string, this looks like a simple, built in, non-VBA way to alter the text to whatever you want (except a true blank)

I know that is a completely different approach than what has been discussed, but will that be adequate?

Your question assumes that I know more about manipulating pivot tables than you do. I did not know about this "custom name" option until today looking over your sample pivot table, right clicking on stuff until I found the "field settings" dialog, and found the custom name field in that dialog. My only recommendation at this point is to continue exploring option dialogs to see if an option for "don't combine multiple level column headings into a pivot chart legend entry" exists anywhere. To my knowledge, it does not exist, but I am not that familiar with pivot tables/charts.