How to set Excels pivot data as default SUM instead of COUNT

R

Roger Govier

Hi Pela

The way the PT works, is if the column of data is numeric, then it defaults
to Sum.
If the data is Text (or a numeric column has Text within it or blank cells),
then it defaults to Count.
Therefore, if you set your data range for the PT to be larger than your
existing data, to allow for future entries) you will always get the PT
defaulting to Count.

If you are using XL2007, then it is best to use Insert tab>Table then use
the table as the source for the PT.
If you are using XL2003, then use Data>List and Create the PT from the List
created.
Both Table and List are Dynamic, and will grow as you add more rows of data.

If you are using XL2002 or earlier, then create your own Dynamic Named
ranges to use as a source for your PT.
There is more information on how to do this here
http://www.contextures.com/xlNames03.html
including a macro to automatically generate dynamic ranges.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot captions and VBA to sum instead of count 1
Pivot table calculated field Count(field) / sum(field) 0
Pivot using 250,000 rows of data in Excel? 1
Pivot tables - how do I change default from "count of" to "sum of" 2
default pivot tabel from count to sum 1
Pivot Table Defaults 1
Access #Error & Excel Pivot 0
Pivot Table default Value Field Settings 1

Top