Prev: Protect/unprotect ALL worksheets in workbook ?
Next: custom number format
From: Nate on 4 May 2010 13:49
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?
From: Jim Thomlinson on 4 May 2010 13:58
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.
--
HTH...

Jim Thomlinson


"Nate" wrote:

> 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?
From: Nate on 4 May 2010 14:19
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" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nate" wrote:
>
> > 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?
From: Jim Thomlinson on 4 May 2010 14:38
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.

--
HTH...

Jim Thomlinson


"Nate" wrote:

> 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" wrote:
>
> > 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.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nate" wrote:
> >
> > > 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?
From: Nate on 4 May 2010 15:00
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" wrote:

> 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.
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nate" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Nate" wrote:
> > >
> > > > 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?
 |  Next  |  Last
Pages: 1 2
Prev: Protect/unprotect ALL worksheets in workbook ?
Next: custom number format