Pivot Table Column Totals - XL2003

[Nate]

I have a pivot table that only has column header and data fields - no row
area. I would like it to show totals for each column, but nothing happens
when I check Grand Totals for Columns in the table options menu?

 

[Jim Thomlinson]

I do not understand the question. If all you have is column headers and data
fields then what are you trying to add up. Each field will only have one row
of data so there is nothing to total unless you wanted to aggregate the
values of the different fields. If that is the case then you need to use a
calculated field to aggregate as the pivot table will not aggregate different
fields by default.

By way of an expanation fields would typically be different measures like
Units Dollars and Weight. You would not add all of those things together as
it makes no sense.

 

[Nate]

The column headers are weeks. There are 10 data fields that are just counts
of call results. The grand totals for columns isn't working for some reason.

Date
5/4/10 - 5/11/10 - 5/18/10 - 5/25/10 -
6/1/10 -
Data 5/10/10 5/17/10 5/24/10 5/31/10
6/5/10
No Answer 2 1 5 6
Left Message 3 0 2 4
Inactive 1 3 3 5
Not Interested 5 0 7 4
Callback

Thanks

 

[Jim Thomlinson]

Grand total does not aggregate different fields together. It is the grand
total of all elements for a single field. To do what you want to do you need
a calculated field. To create the field on the Pivot Table toolbar select
Pivot Table | Formulas | Calculated Field | name the new field Total and Add
together all of the different fields.

To further explain lets assume you have a pivot table with 2 fields Sales
Units and Sales Dollars. If I place both of those fields on a pivot table and
add the Grand Totals it would be completely inappropriate for the pivot table
to add the 2 fields together. It will do a grand total for each. i suspect
that your data source is set up somewhat incorrectly for you purpose and that
is where you are running into problems. It should look like this...

Date Call Type Count
1-Jan No Answer 3
1-Jan Message 5

If it looked like that then your grand total would be the total of all of
the single Call Type field and you would not need a calculated field.

 

[Nate]

Thanks Jim. The data source is not setup to accomodate a pivot table, but
it's not my workbook so I was just trying to do it based off what they
already had in there. I tried doing a calculated field, but I ran out of
space when I was adding all the fields together. First time that's happened
to me. Guess I'll just do a table manually. Thanks for the info.

 

[Jim Thomlinson]

I have never ran out of room but if that is the case then you could create a
couple of calc fileds and then add those fields together. Just a thought.