Pivot table subtotals

[RobE]

Is there a way to keep the subtotals of a pivot table displayed but hide the individual lines?

 

[Tom@CPC]

RobE,

Yes there is. And you can do it for one subtotal in your table or for all subtotals.

To hide the detail for just one level or group, you can either double click on the group name, or right click on the name and from the pop up menu, select Hide Detail.

If you want to hide the detail for all levels, right click on the column header for that level and select Hide Detail again.

To show the detail once again, either double click the level once again or right click and select Show Detail.

HTH, Regards
Tom

 

[RobE]

When you hide the detail it also hides the subtotals
Can you leave the subtotals there?

 

[Tom@CPC]

RobE,

If I understand you correctly, the subtotal is just the sum of all the detail lines, so when you remove the detail portions, the line that is reported is the subtotal, it just does not get described as that, it takes on the name of the level that you are hiding.

Look at the value of your subtotal, then hide the detail, and you will see that they are the same value.

Alternatively, it could be that your subtotals are turned off. When you right click on a level, go to Field Settings, and you can turn subtotals on or off, select a different summary (min, max etc..) and even sort by a field or select how many records to show (top 10, bottom 10 etc...)

 

[Mark W.]

When you Hide the details the "subtotal" becomes the "total"...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Fruit	Month	Qty		SumofQty				SumofQty
2	Apples	Jan	100		Fruit	Month	Total		Fruit	Month	Total
3	Bananas	Jan	35		Apples	Jan	100		Apples		138
4	Grapes	Jan	41			Feb	38		Bananas	Jan	35
5	Oranges	Jan	62		ApplesTotal		138			Feb	32
6	Apples	Feb	38		Bananas	Jan	35		BananasTotal		67
7	Bananas	Feb	32			Feb	32		Grapes	Jan	41
8	Grapes	Feb	91		BananasTotal		67			Feb	91
9	Oranges	Feb	87		Grapes	Jan	41		GrapesTotal		132
10						Feb	91		Oranges	Jan	62
11					GrapesTotal		132			Feb	87
12					Oranges	Jan	62		OrangesTotal		149
13						Feb	87		GrandTotal		486
14					OrangesTotal		149
15					GrandTotal		486
16
Sheet1

 

[Tom@CPC]

RobE,

While at coffee the thought occured to me that you may be "Hiding" at too high of a level. Taking Mark's example 1 step further, if you had additional information that detailed the months by week (ie: 1,2,3,4) and you included that data in your pivot table, then to Hide the Weekly detail, you would click on Month. The value in the body of the table is your monthly subtotal now rather than the weekly detail.

If you wanted to just show Fruits, click on Month and Hide it. The body of the table is now the subtotals by fruit.

HTH, Tom