Pivot table - value field settings - default?
 

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!

Answer: Pivot table - value field settings - default?
 

Yes, there is a way to set the default value field in a pivot table. Here's how you can do it:

1. Create a new pivot table or open an existing one.
2. Drag the field you want to use as the default value field to the "Values" area of the pivot table.
3. Click on the drop-down arrow next to the field name in the "Values" area and select "Value Field Settings".
4. In the "Value Field Settings" dialog box, select the function you want to use as the default (e.g. SUM, COUNT, AVERAGE, etc.).
5. Click on the "OK" button to save the changes.

Now, every time you add a new field to the "Values" area of the pivot table, it will automatically use the default function you selected in step 4. You won't have to manually change it every time.

Pivot table - value field settings - default?
 

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 DataList in XL2003 or Insert
tabTable for XL2007. I will describe the position for XL2007
Insert tabTableclick my table has HeadersOK
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!