ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Create Pivot Table Data with Column "Sum" rather than "count" defa (https://www.excelbanter.com/excel-discussion-misc-queries/252211-create-pivot-table-data-column-sum-rather-than-count-defa.html)

Johnny_99[_2_]

Create Pivot Table Data with Column "Sum" rather than "count" defa
 
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

Create Pivot Table Data with Column "Sum" rather than "count" defa
 
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

Johnny_99[_2_]

Create Pivot Table Data with Column "Sum" rather than "count"
 
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
.



All times are GMT +1. The time now is 04:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com