Excel 2002 Bug? Autoshow Limits at 255

  • Thread starter Thread starter Chuck Hildebrandt
  • Start date Start date
C

Chuck Hildebrandt

I am doing a pivot table which has about 2,000 line items. The
highest volume line items have several thousand widgets; many line
itmes have only 1 or 2. I wanted to limit the PVT to only those with
100 or more widgets.

There is no filter in PVT for showing only those line items with 100
or more widgets, so I tried the next best thing: I wanted to limit it
to the top 420 line itmes, all of which have 100 or more widgets.

I went to line items' field settings advanced tab, which has the
Autoshow function. It defaults to Top 10, but has another option to
specify a different number of top line items. I chose 420, but only
164 line items were returned.

I played around with different numbers here and there, and came to the
conclusion that this function has an 8-bit limit to it: it will only
return the top 255 line items. If you ask for the top 256, it returns
zero; top 257 returns 1; top 258 returns 2; and so on.

It is supposed to return a limit up to 500, but it won't do so because
of the problem I describe above.

Does anyone recognize this problem and can you suggest a fix? Is it
unique to Excel 2002?

Thanks.

Chuck
 
Interesting. It's the same in Excel 97, 2000 and 2003.

As a workaround, you could add a field to your data table, in which you
calculate whether the item is greater than 100. For example:

=SUMIF($A$2:$A$4000,A2,$G$2:$G$4000)>500

Add that field to the page area, and filter for TRUE.
 
Back
Top