Pivot Defaults

P

Pete

Hi,

Is it possible to change the defaults for Pivot Charts?
for example - to set 'Sub totals' and 'Grand Totals'
to 'None' by default. or set the default to average of
value rather than sum of value etc.

Also is it possible to make the Row fields display data
for every line? as far as I can see the only option is to
merge lables or not. e.g.

labelA1 labelB1
labelB2
labelA2 labelB1
labelB2
labelB3

to be displayed as:

labelA1 labelB1
labelA1 labelB2
labelA2 labelB1
labelA2 labelB2
labelA2 labelB3

many thanks

Pete
 
D

Debra Dalgleish

Row and column headings are displayed once in the pivot table, and
there's no setting you can change to make them appear for each item.

AFAIK you can't change the defaults for totals, but you could run a
macro to turn them off. There's sample code here to turn off the subtotals:

http://www.contextures.com/xlPivot03.html#Subtotals

You could add lines to turn off the grand totals. For example:

'=========================
Sub NoSubtotalsOrGrandTotals()
'turns off subtotals and grand totals
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
With pt
.ColumnGrand = False
.RowGrand = False
End With
Next pt

End Sub
'========================
 
Top