Change the word Total in a Pivot Table

[SammyB]

In my Pivot Table I have both the Sum & the Std Dev:

Code: Select all

pt.AddDataField(pt.PivotFields("Amount"), "Sums", xlSum)
pt.AddDataField(pt.PivotFields("Amount"), "Std Devs", xlStDevP)

So, the last two columns are labeled "Total Sums", "Total Std Devs"
Total Sums is confusing & Total Std Devs makes no sense. I would like to have the column labels be Total & Overall Std Dev.
I was excited to find PivotTable.GrandTotalName, but that onlt changes the row label on the last row.

Eithe a VBA or manual solution would be fine, but Excel says I cannot edit it when I try manually.

[HansV]

1) Which version of Excel are you using?
2) What is the report layout of your pivot table? Compact, outline of tabular?

S2521.png

[SammyB]

Microsoft 365
Compact: .RowAxisLayout xlCompactRow
All the options:

Code: Select all

        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow

[HansV]

I created a small pivot table with those settings, and I see this:

S2522.png

Could you attach a sample workbook demonstrating your problem?

[SammyB]

That would be lovely, but just add dates to your data over say a three month period & have Month in Columns. Then you will see Total of Sums & Total of STD Devs. I'll make a workbook like that & attach it in the next post.

[SammyB]

Sample Attached.

[HansV]

Thanks - I see what you mean now. As far as I can tell, those captions are read-only, both in the interface and in VBA...

[SammyB]

It's brute force then: hide the rotten title row & create a good one! lol Thanks, good to have confirmation.