Pivot table subtotals

RobE

New Member
Joined
Apr 3, 2002
Messages
23
Is there a way to keep the subtotals of a pivot table displayed but hide the individual lines?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Tom@CPC

Board Regular
Joined
May 22, 2002
Messages
209
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

New Member
Joined
Apr 3, 2002
Messages
23
When you hide the detail it also hides the subtotals
Can you leave the subtotals there?
 

Tom@CPC

Board Regular
Joined
May 22, 2002
Messages
209
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
When you Hide the details the "subtotal" becomes the "total"...
Book1
ABCDEFGHIJKL
1FruitMonthQtySumofQtySumofQty
2ApplesJan100FruitMonthTotalFruitMonthTotal
3BananasJan35ApplesJan100Apples138
4GrapesJan41Feb38BananasJan35
5OrangesJan62ApplesTotal138Feb32
6ApplesFeb38BananasJan35BananasTotal67
7BananasFeb32Feb32GrapesJan41
8GrapesFeb91BananasTotal67Feb91
9OrangesFeb87GrapesJan41GrapesTotal132
10Feb91OrangesJan62
11GrapesTotal132Feb87
12OrangesJan62OrangesTotal149
13Feb87GrandTotal486
14OrangesTotal149
15GrandTotal486
16
Sheet1
 

Tom@CPC

Board Regular
Joined
May 22, 2002
Messages
209
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
 

Forum statistics

Threads
1,144,449
Messages
5,724,414
Members
422,552
Latest member
Ajit Kumar_1982

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top