Pivot Table Defaults



In the pivot table field list, whenever I create a new pivot table and I am
inserting fields into the value area, I generally get as default field
setting the 'Count' value. Is there a way to format the spreadsheet to make
Excel recognize the data as all numbers so it defaults to the "Sum" function
as opposed to "text"?

Roger Govier


The rule that the PT Wizard adopts is,
If all the values in the field being added to the data area are Numeric,
then it uses Sum.
If any of the values are Text or BLANK, then it uses Count.

It sounds as though you have defined a large range for your source data,
to allow for future entries. Of necessity, this will include blank cells
in your numeric fields.

If you are using XL2003, place your cursor in your source data and use
Data>List>Create>check my list has Headers.
The List will grow dynamically as you add more data.
Now, make the list the source data for your PT, and it will default to
Sum for your Numeric data

If you are using XL2007, then similar to above, Insert tab>Table>check
my table has headers
Click on Table>options tab>Summarize with Pivot Table

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