Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
In 2003, we had a formatting option that would globally remove Sum Of from
the pivot table values. I like that feature. However, I cant seem to find the same thing in 2007. I can manually change them, but thats no fun, especially if I have a bunch of values. Does anyone have solution to this question? Thanks |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Hi Russ,
I don't know which command you mean but maybe one of these sites might help you out. http://www.rondebruin.nl/0307commands.htm http://office.microsoft.com/en-us/he...491511033.aspx HTH Martin "Russ" wrote in message ... 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? Thanks |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
I can't help but I'd love to know how to access this in Excel 2003! Its not an option I am aware of. Mike On Jun 27, 8:03*pm, Russ wrote: In 2003, we had a formatting option that would globally remove Sum Of from the pivot table values. *I like that feature. *However, I cant seem to find the same thing in 2007. *I can manually change them, but thats no fun, especially if I have a bunch of values. * Does anyone have solution to this question? Thanks |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Hi Russ
I am totally unaware of any in built function in XL2003 that would do this, and there is no such function in XL2007. However, 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 InsertModule 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 -- Regards Roger Govier "Russ" wrote in message ... In 2003, we had a formatting option that would globally remove Sum Of from the pivot table values. I like that feature. However, I cant seem to find the same thing in 2007. I can manually change them, but thats no fun, especially if I have a bunch of values. Does anyone have solution to this question? Thanks |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Thanks. I will give this a try.
To all, 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. This is what I'm looking for. My users don't need to see "Sum of". That's obvious to them. These can be manually removed, but there should be a global functionality that was in Excel 2003. "Roger Govier" wrote: Hi Russ I am totally unaware of any in built function in XL2003 that would do this, and there is no such function in XL2007. However, 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 InsertModule 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 -- Regards Roger Govier "Russ" wrote in message ... In 2003, we had a formatting option that would globally remove Sum Of from the pivot table values. I like that feature. However, I cant seem to find the same thing in 2007. I can manually change them, but thats no fun, especially if I have a bunch of values. Does anyone have solution to this question? Thanks |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Thanks for the references. I'll check them out.
To all, 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. This is what I'm looking for. My users don't need to see "Sum of". That's obvious to them. These can be manually removed, but there should be a global functionality that was in Excel 2003. "MartinW" wrote: Hi Russ, I don't know which command you mean but maybe one of these sites might help you out. http://www.rondebruin.nl/0307commands.htm http://office.microsoft.com/en-us/he...491511033.aspx HTH Martin "Russ" wrote in message ... 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? Thanks |
#7
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Hi Martin,
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. Try one of these and you'll see what I'm talking about. Thanks. Russ "MartinW" wrote: Hi Russ, I don't know which command you mean but maybe one of these sites might help you out. http://www.rondebruin.nl/0307commands.htm http://office.microsoft.com/en-us/he...491511033.aspx HTH Martin "Russ" wrote in message ... 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? Thanks |
#8
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Hi Russ
Since I seldom, if ever, use Autoformat, I was totally unaware that it would remove the "Sum of". Thanks for pointing that out. The macro I posted should achieve what you want You will note that the macro leaves a space in front of the field name. That is quite deliberate, as you cannot have a PT field name the same as a Source data field name in the Data area - the reason why Sum of and Count of etc. are added in the first instance. You can easily amend the code to deal with Count of etc., should you have the need. -- Regards Roger Govier "Russ" wrote in message ... Thanks. I will give this a try. To all, 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. This is what I'm looking for. My users don't need to see "Sum of". That's obvious to them. These can be manually removed, but there should be a global functionality that was in Excel 2003. "Roger Govier" wrote: Hi Russ I am totally unaware of any in built function in XL2003 that would do this, and there is no such function in XL2007. However, 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 InsertModule 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 -- Regards Roger Govier "Russ" wrote in message ... In 2003, we had a formatting option that would globally remove Sum Of from the pivot table values. I like that feature. However, I cant seem to find the same thing in 2007. I can manually change them, but thats no fun, especially if I have a bunch of values. Does anyone have solution to this question? Thanks |
#9
Posted to microsoft.public.excel.setup
|
|||
|
|||
Removing "Sum of" from pivot tables
Thanks. I'll give this a whirl.
Cheers, Russ "Roger Govier" wrote: Hi Russ Since I seldom, if ever, use Autoformat, I was totally unaware that it would remove the "Sum of". Thanks for pointing that out. The macro I posted should achieve what you want You will note that the macro leaves a space in front of the field name. That is quite deliberate, as you cannot have a PT field name the same as a Source data field name in the Data area - the reason why Sum of and Count of etc. are added in the first instance. You can easily amend the code to deal with Count of etc., should you have the need. -- Regards Roger Govier "Russ" wrote in message ... Thanks. I will give this a try. To all, 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. This is what I'm looking for. My users don't need to see "Sum of". That's obvious to them. These can be manually removed, but there should be a global functionality that was in Excel 2003. "Roger Govier" wrote: Hi Russ I am totally unaware of any in built function in XL2003 that would do this, and there is no such function in XL2007. However, 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 InsertModule 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 -- Regards Roger Govier "Russ" wrote in message ... In 2003, we had a formatting option that would globally remove Sum Of from the pivot table values. I like that feature. However, I cant seem to find the same thing in 2007. I can manually change them, but thats no fun, especially if I have a bunch of values. Does anyone have solution to this question? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing "Sum of" from pivot tables | Excel Discussion (Misc queries) | |||
Pivot Tables show actual name rather then "Column Labels" | Excel Discussion (Misc queries) | |||
Avoid "update Links" promt in e-mailed pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Tables - How can I "reset" the selections in "Row Field"? | Excel Discussion (Misc queries) | |||
How do I disable "Get Pivot Data" when working from pivot tables? | Excel Worksheet Functions |