Changing Pivot table defaults

[Steve Antoniades]

I am using excel 2003 and did something to change a default and I don't know
how to set it back. Whenever I create a pivot table and drag a field into
the data area the default behavior is to count the items. I thought it used
to sum them by default. Does anyone know how I can change the default back
to summing the data?

Thanks!
S

 

[Dave Peterson]

If all the data in the field is numeric, then you'll see Sum.

If any of the data in the field is text (or empty!), then you'll see Count.

I don't think you can change this behavior.

But you can use Debra Dalgleish's pivottable addin to lots of stuff:
http://contextures.com/xlPivotAddIn.html

 

[Shane Devenshire]

Hi,

There is no built in way to control this. However, you could write VBA code
to do it, is that what you want?

By the way in 2007 the situation is slightly different - blank cells do not
cause Count, only non-numeric entries. And in 2007 you still don't have
control of the default behavior.

If you fill in all blank cells in your data range with 0's in 2003 then
there won't be any blanks to generate the Count. It might be an option
also?

Cheers,
Shane

 

[BK]

If you right click the "count of" field, you will have the option of
changing it to "sum."

 

[Steve Antoniades]

Thanks everyone for the responses.

Now I know what is causing it! I can fix it as I import the data I am
using.

Steve

Hi,

There is no built in way to control this. However, you could write VBA
code to do it, is that what you want?

By the way in 2007 the situation is slightly different - blank cells do
not cause Count, only non-numeric entries. And in 2007 you still don't
have control of the default behavior.

If you fill in all blank cells in your data range with 0's in 2003 then
there won't be any blanks to generate the Count. It might be an option
also?

Cheers,
Shane