Pivot Table Show Top 10 only works on first group?

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
536
I need to create a chart for each group of data in my Pivot Table. The far left column shows Years. Each Year will have a chart. For each Year I need to show only the top ten Items in the second column of the table. When I select the field column label for the Items and set AutoShow to automatically show Top 10, only the first (top) group of Items reduces to 10 items. All Years below the first group show all the Items. Is there a way to make Autoshow apply to all groups of Items?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Move 'Year' to your PivotTable's PAGE area and create a separate PivoTable (1 for each chart) using the Show Pages tool. See the Excel Help topic for "Display PivotTable page field pages on separate worksheets".
 
Upvote 0
Thanks Mark - Unfortunately the result was the same. I re-created the Pivot Table from scratch... and then followed the Help instructions for Show Pages. Then on each Pivot Table Page I turned on the AutoShow Top 10 option for the Item Label. Only the Pivot Table on the first page reduced to show only 10 items. Strangely, the text on the Item Labels on every Page changed to blue each time as soon as I turned on AutoShow, but only the first table actually hid the Items beyond the top 10 in the Totals column. I'm stumped.
 
Upvote 0
On 2002-08-23 15:02, RogerC wrote:
Thanks Mark - Unfortunately the result was the same...

1. I re-created the Pivot Table from scratch...
2. and then followed the Help instructions for Show Pages.
3. Then on each Pivot Table Page I turned on the AutoShow Top 10 option for the Item Label.

...Only the Pivot Table on the first page reduced to show only 10 items. Strangely, the text on the Item Labels on every Page changed to blue each time as soon as I turned on AutoShow, but only the first table actually hid the Items beyond the top 10 in the Totals column. I'm stumped.

You should have done Step #2 (above) after Step #3 (above).
 
Upvote 0
On 2002-08-23 15:02, RogerC wrote:
Thanks Mark - Unfortunately the result was the same...

1. I re-created the Pivot Table from scratch...
2. and then followed the Help instructions for Show Pages.
3. Then on each Pivot Table Page I turned on the AutoShow Top 10 option for the Item Label.

...Only the Pivot Table on the first page reduced to show only 10 items. Strangely, the text on the Item Labels on every Page changed to blue each time as soon as I turned on AutoShow, but only the first table actually hid the Items beyond the top 10 in the Totals column. I'm stumped.

You should have done Step #2 (above) after Step #3 (above).

I just did it for this data set...
Book1
ABCD
1FruitMonthQty
2ApplesMay137
3BananasJan35
4GrapesApr188
5OrangesNov141
6ApplesOct165
7BananasJun127
8GrapesApr220
9OrangesMar61
10ApplesApr126
11BananasNov20
12GrapesFeb236
13OrangesSep45
14ApplesOct70
15BananasFeb153
16GrapesAug122
17OrangesMay44
18ApplesMar82
19BananasApr33
20GrapesMar121
21OrangesFeb69
22ApplesOct231
23BananasMar145
24GrapesApr174
25OrangesJan54
26
Sheet1


...and produced Top3 PivotTables such as...
Book1
ABCD
1FruitApples
2
3SumofQty
4MonthTotal
5Apr126
6May137
7Oct466
8GrandTotal729
Apples

This message was edited by Mark W. on 2002-08-23 15:48
 
Upvote 0
Thanks Mark - Through your example I realized what's wrong. I mis-interpreted what AutoShow is supposed to display. I thought it would show the Top 10 Items (rows) in the list regardless of their values in the Totals column. Because I have many Items with the same value in the Totals column, and AutoShow is based on the Count of Items, I see now that a Top 10 list may have many more than 10 rows. Unfortunately, I'm back to my original problem. I need to chart no more than the Top 10 ROWS, regardless of their Count value. Only Count of Item is available under the AutoShow Using Field selection box, Item is not.
Any suggestions?
 
Upvote 0

Forum statistics

Threads
1,218,945
Messages
6,145,363
Members
450,611
Latest member
JodiWe

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
Back
Top