I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?

If you have any non-numeric data in a field (empty cells are non-numeric!), then
excel will use Count.

But you could take a look at Debra Dalgleish's pivottable addin.
http://contextures.com/xlPivotAddIn.html
http://contextures.com/xlPivotAddIn02.html

It has lots of nice features--including one you'll really like.

Johnny_99 wrote:

I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?

--

Dave Peterson

Thanks Dave. and the add-in is also quite helpful.

John ...

"Dave Peterson" wrote:

If you have any non-numeric data in a field (empty cells are non-numeric!), then
excel will use Count.

But you could take a look at Debra Dalgleish's pivottable addin.
http://contextures.com/xlPivotAddIn.html
http://contextures.com/xlPivotAddIn02.html

It has lots of nice features--including one you'll really like.

Johnny_99 wrote:

I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?

--

Dave Peterson
.