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

Yes, there is a way to remove "Sum of" from pivot tables in Excel 2007. Here's how:

1. Click on any cell within the pivot table.
2. In the PivotTable Tools ribbon, click on the Options tab.
3. In the Active Field group, click on the Field Settings button.
4. In the Value Field Settings dialog box, click on the "Number Format" button.
5. In the Format Cells dialog box, click on the "Custom" category.
6. In the "Type" field, delete the "Sum of" text and any other unwanted text.
7. Click OK to close all dialog boxes.

Now, all the values in the pivot table should display without the "Sum of" text. If you have multiple values in the pivot table, you can repeat these steps for each value.

I don't know of any option in Excel 2003 that removes the 'Sum Of' from
data fields. Maybe you had a macro that changed the captions, and it was
lost when you upgraded to 2007.

You could create another macro, to run in Excel 2007.

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



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

The auto format in Excel 2003 would enable the functionality I'm currently
pursuing in 2007. It also enabled the capability to go between a classic
format and the provided auto formats. No macros required.

"Debra Dalgleish" wrote:

I don't know of any option in Excel 2003 that removes the 'Sum Of' from
data fields. Maybe you had a macro that changed the captions, and it was
lost when you upgraded to 2007.

You could create another macro, to run in Excel 2007.

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



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Hi, Just thought I owed you a better explanation of what I'm pursuing.

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.

Thanks.

"Debra Dalgleish" wrote:

I don't know of any option in Excel 2003 that removes the 'Sum Of' from
data fields. Maybe you had a macro that changed the captions, and it was
lost when you upgraded to 2007.

You could create another macro, to run in Excel 2007.

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



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Thanks for the detail on what you did in Excel 2003.

The AutoFormat command had some features that weren't accessible through
other methods, like changing the captions to the same as the source
name, and altering the heading rows.

AutoFormat has been replaced by PivotTable Styles in Excel 2007, and
they don't make the same kinds of changes to the pivot table structure.

If you can run a macro, you could use something like this:

Sub ChangeDataCaptions()
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.DataFields
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = pf.SourceName & " "
End If
Next pf

End Sub


Russ wrote:
Hi, Just thought I owed you a better explanation of what I'm pursuing.

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.

Thanks.

"Debra Dalgleish" wrote:


I don't know of any option in Excel 2003 that removes the 'Sum Of' from
data fields. Maybe you had a macro that changed the captions, and it was
lost when you upgraded to 2007.

You could create another macro, to run in Excel 2007.

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



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com