Pivot Table with default "Sum of" insted of "Count of"

[gregm56]

Is there a way to have the values default to "Sum of" instead of "Count of" in Excel 2007.

It gets tiresome to have to change them one at a time. Is there way to change multiple value fields at the same time? Haven't found a way and Excel "help" isn't much .

Any help would be most appreciated!

 

[taigovinda]

All you have to do is make sure that your raw data contains an entry in every cell, and all the entries are numerical.

What that means is basically that after your raw data is in place, select all of it, goto special blanks (control+g, alt+s, k, enter), type 0 and press control+enter. This fills all your blank cells with zero and now your pivot defaults to sum of.

You could also pursue a VBA solution to set the data field settings to sum programatically, but it wouldn't be a "setting" per say, it would be code that you would have to run - and a macro that would have to be stored somewhere.

 

[xenou]

The why is that if you have any blank cells in the data being aggregated, the default is Count. If all cells are numeric values, the default is Sum.

I don't know of any way around this - but sometimes if you are able to select the data area only, it comes out right (other times, its desirable to use the entire columns so that you can put any amount of data in ... so its a tradeoff there).

ξ

 

[taigovinda]

The why is that if you have any blank cells in the data being aggregated, the default is Count. If all cells are numeric values, the default is Sum.

I don't know of any way around this - but sometimes if you are able to select the data area only, it comes out right (other times, its desirable to use the entire columns so that you can put any amount of data in ... so its a tradeoff there).

ξ

There doesn't have to be a tradeoff, if you just always use a dynamically defined range - e.g. PivotData=offset($A$1,,,counta(A:A),counta(1:1))

 

[gregm56]

Thanks for the pointers. This is helpful!!

 

[xenou]

Good idea, taigovinda!

 

[taigovinda]

Thanks! Got tired awhile ago of choosing between "blanks" and re-assigning data range all the time.

 

[jlongbourne]

I found that in addition to replacing blanks (or text) with zeroes there was a quirk in the last row of my data, that needed a work-around.
(I think it's just a quirk of the corporate system that was generating the excel output.)
If I selected all the data when creating the PivotTable, it would default to Count. If I selected any number of rows up to, but not including the last row, it would default to Sum (odd indeed...)
The work around was to copy and paste the second-to-last row of data below the last row, and then edit the data in this dummy row so it was all zeroes.
Then miraculously, selecting all the data again (including the extra dummy row) did make the PivotTable default to Sum!

 

[edchoy01]

I found that in addition to replacing blanks (or text) with zeroes there was a quirk in the last row of my data, that needed a work-around.
(I think it's just a quirk of the corporate system that was generating the excel output.)
If I selected all the data when creating the PivotTable, it would default to Count. If I selected any number of rows up to, but not including the last row, it would default to Sum (odd indeed...)
The work around was to copy and paste the second-to-last row of data below the last row, and then edit the data in this dummy row so it was all zeroes.
Then miraculously, selecting all the data again (including the extra dummy row) did make the PivotTable default to Sum!

It is odd. I have the same issue except with the last column instead of row. My work around was the same. At first I just added a dummy column with a heading and zeros at the end and then selected up the second to the last row but that did not work. I had to actually copy and and insert the last row to make it work