Prev: Must I use IRM to protect an entire workbook?
Next: shrink excel page to print out standard size
From: smartgal on 11 May 2010 18:49
Is there a way to set the value field to a default, like SUM versus COUNT?
Having to redo this on every single value in the pivot table is irritating .
.. . help!

Thanks!
From: Roger Govier on 12 May 2010 03:48
Hi

It is a function of the data that is held within the field.
If ALL the data is numeric, then the PT will default to Sum.
If ANY of the data is Text OR Blank, then the PT will default to Count.

Perhaps your source range is set larger than the actual data set, to
allow for more entries. In this scenario, you will always get a default
of Count.

If that is the case, then either use Data>List in XL2003 or Insert
tab>Table for XL2007. I will describe the position for XL2007
Insert tab>Table>click my table has Headers>OK
A new tab will appear title Design.
In the first section, Properties, there is a Table name which will
default to Table1. You may rename to whatever you want.
In the Tools section of the Design tab, choose the Option Summarise with
Pivot Table.
The PT will now be based upon the table, and the table will
automatically resize as you add or remove data, and it will not contain
any blank lines.

For ANY version of Excel, you can define a Dynamic Range for yourself,
and use this as the source for your PT.
Take a look at a tutorial I wrote on creating Dynamic Ranges at
http://www.contextures.com/xlNames03.html

You can use VBA to run through a Pivot Table and change all Data values
to Sum.
Post back if you want a VBA solution.
--
Regards
Roger Govier

smartgal wrote:
> Is there a way to set the value field to a default, like SUM versus COUNT?
> Having to redo this on every single value in the pivot table is irritating .
> . . help!
>
> Thanks!
 | 
Pages: 1
Prev: Must I use IRM to protect an entire workbook?
Next: shrink excel page to print out standard size