If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|