When I create a pivot table in Excel 2007, the value defaults to Count. Is there any way to change the default to Sum? I’m adding numbers of units sold, for example.
StG
It will default to sum if every value in the field/column is a a number.
A single blank or text entry in the range causes the default to be count.
penultima thule - But every entry in that field is a number, whether formatted as number or currency. That’s why it’s so frustrating having to change the value field each time I create a new pivot table.
StG
Try this. Select the entire column containing the numbers. On the Data tab click Text to Columns. Make sure that Delimited is selected, then click the Finish button.
This solves a lot of mysterious Excel formatting issues.
Or you could do it one of the correct ways in 2007. Let’s say your column in your source data with the numbers you want to sum is RECORDS. In the top left corner of the Pivot Table you should see a cell that says Count of RECORDS. Right Click that cell and one of the options will be Value Field Settings… select Sum and hit OK.
The other way is to go to the Pivot Table Field List Box. There should be a list of all the fields in your source data and boxes below where you can drag fields into either rows, columns, and Values. Your field RECORDS should be in the Values box with a little drop down arrow next to the field name. Click it and the same list will appear as my first suggestion. Again, go to Value Field Settings and pick Sum. You can also pick Average, Max, Min, etc.
D’oh… I just re-read the OP and you want it to DEFAULT rather than how to change it. I was really surprised the others didn’t know something so simple. Turns out I’m the one who needs to go back and learn how to read.
Spud - Thanks for the attempts, anyway.
dataguy - Honestly, doing text to columns is more of a pain that resetting the field values.
It just seems like you should be able to go into Options and set this, doesn’t it? But you can’t as fas as I can tell.
StG