pivot table subtotals
 

i have a report which has 250 columns and 1000 rows. i'm creating various
pivot tables with the data in the columns. if i create a pivot table on
height (column) and weight (row), i get the data i need. is there a way to
create a subtotal on just the weight 150? all of the weights are in actual
lbs, so i've got a range from 101 lbs to 212. it's a bit cumbersome to
uncheck or check all of the fields in the pivot table to filter it out.
thanks.

pivot table subtotals
 

Hi Ann,

Weight is a row field, yes?

If so put your cursor in the weight field of the pivot table and choose the
command Pivot Table, Group and show detail, Group. Set the by to 150. This
may do exactly what you want, if not you can create a calculated field or
item which takes longer to set up. Yet a third method would be to modify the
data source to include a new field Over 150, use a formula like =B1150 then
this now field with return TRUE if over 150 otherwise FALSE. Put this field
in the pivot table as a row field to the left of the weight field.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Ann" wrote:

i have a report which has 250 columns and 1000 rows. i'm creating various
pivot tables with the data in the columns. if i create a pivot table on
height (column) and weight (row), i get the data i need. is there a way to
create a subtotal on just the weight 150? all of the weights are in actual
lbs, so i've got a range from 101 lbs to 212. it's a bit cumbersome to
uncheck or check all of the fields in the pivot table to filter it out.
thanks.

pivot table subtotals
 

when i click group, i get an error: cannot group that selection. how do you
set it to 150?

"ShaneDevenshire" wrote:

Hi Ann,

Weight is a row field, yes?

If so put your cursor in the weight field of the pivot table and choose the
command Pivot Table, Group and show detail, Group. Set the by to 150. This
may do exactly what you want, if not you can create a calculated field or
item which takes longer to set up. Yet a third method would be to modify the
data source to include a new field Over 150, use a formula like =B1150 then
this now field with return TRUE if over 150 otherwise FALSE. Put this field
in the pivot table as a row field to the left of the weight field.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Ann" wrote:

i have a report which has 250 columns and 1000 rows. i'm creating various
pivot tables with the data in the columns. if i create a pivot table on
height (column) and weight (row), i get the data i need. is there a way to
create a subtotal on just the weight 150? all of the weights are in actual
lbs, so i've got a range from 101 lbs to 212. it's a bit cumbersome to
uncheck or check all of the fields in the pivot table to filter it out.
thanks.

pivot table subtotals
 

Sorry I didn't look back at this sooner.

If you can't group the row field containing weights it suggests that this
field is not a numerical field. I suggest you convert the original source
data to numbers and then try again. If not send me a copy.


--
Cheers,
Shane Devenshire


"Ann" wrote:

when i click group, i get an error: cannot group that selection. how do you
set it to 150?

"ShaneDevenshire" wrote:

Hi Ann,

Weight is a row field, yes?

If so put your cursor in the weight field of the pivot table and choose the
command Pivot Table, Group and show detail, Group. Set the by to 150. This
may do exactly what you want, if not you can create a calculated field or
item which takes longer to set up. Yet a third method would be to modify the
data source to include a new field Over 150, use a formula like =B1150 then
this now field with return TRUE if over 150 otherwise FALSE. Put this field
in the pivot table as a row field to the left of the weight field.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Ann" wrote:

i have a report which has 250 columns and 1000 rows. i'm creating various
pivot tables with the data in the columns. if i create a pivot table on
height (column) and weight (row), i get the data i need. is there a way to
create a subtotal on just the weight 150? all of the weights are in actual
lbs, so i've got a range from 101 lbs to 212. it's a bit cumbersome to
uncheck or check all of the fields in the pivot table to filter it out.
thanks.