Change the word Total in a Pivot Table

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Change the word Total in a Pivot Table

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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change the word Total in a Pivot Table

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: Change the word Total in a Pivot Table

Post by 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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change the word Total in a Pivot Table

Post by 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?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: Change the word Total in a Pivot Table

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

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: Change the word Total in a Pivot Table

Post by SammyB »

Sample Attached.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change the word Total in a Pivot Table

Post by 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... :sad:
Best wishes,
Hans

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: Change the word Total in a Pivot Table

Post by SammyB »

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