PivotTable limits

[brutus6294]

I've created a pivottable. Data file for pivot is very large, approx 15000 rows. Pivot table was created fine, but when I try to add an additional field to the row area of the pivot, I get "Microsoft Excel cannot make this change because there are too many row or column items. I only have 4 row items, 1 column, and 3 data fields. Is it because of the number of data rows in my datafile?

 

[AnotherHiggins]

In Excel's help, search for specifications.

Select the returned item titled Excel specifications and limits.

Select PivotTable report specifications.

There you will find the following info:
[tt]
Feature Maximum limit
PivotTable reports on a sheet Limited by available memory

Unique items per field 32,500

Row or column fields in a Limited by available memory
PivotTable report

Page fields in a PivotTable 256 (may be limited by
report available memory)

Data fields in a PivotTable 256
report

Calculated item formulas in a Limited by available memory
PivotTable report
[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[brutus6294]

Yes, I saw that.. Trying to understand that... Would it still give that message if it were a memory issue? Actually am using only a handful of fields...Am having a hard time grasping 'unique items by field'... Thanks, but am still confused!

 

[xlbo]

I think this is more to do with the calculations used to generate the pivot table but that depends on the version of excel you are using - AFAIK up to xl2002, pivot tables used a hidden sheet (that you cannot access) to calculate all the fields in the pivot table. Dependant on the number of records and the layout of the pivot table, it is possible that the hidden sheet (which has the same limits as a normal excel sheet) does not have enough columns to hold all the calculations for the fields



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

When I run into this message, I try removing a field from some other area (ROW, DATA, COLUMN), add the field in question and then add the one I removed.

Sometimes it works, sometimes it does not.

If it does not work, consider concatenating two or more fields you are using in your ROW area, in the SOURCE DATA.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[brutus6294]

thanks all.. xlbo - running Excel 2003 (fyi)

 

[Gavona]

Row or column fields in a Limited by available memory
PivotTable report

I use Skip's approach with some success. Also simply re-starting my PC and Excel clears that little bit extra memory. But I have to be careful. I can create a pivot table on one PC (at home) that I cannot open at work because of the memory issue.



Gavin

 

[xlbo]

ok - most likely a memory issue then

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question