Pivot Table Problem

[John Calder]

Hi

I use WinXP with Excel 2000

I have a problem with a pivot table

The sheet that contains the raw data has 10 columns of data approx 1200 rows
deep

The 1st 6 columns of data is linked to another spreadsheet. The last four
columns are helper columns that I have added. All columns have unique header
names. This all works fine.

In my pivot table I use:-

2 of my headings in the “Page†area.

3 of my headings in the “Row†area

1 of my headings in the “Data†area

This also works fine.


The problem occurs when I try to add another of my heading to the “Row†area.

I get the following error message:-

Microsoft Excel cannot make this change because there are too many row or
column items. Drag at least one row or column field off the Pivot Table, or
to the page position. Alternatively right click a field and then click Hide
or Hide Levels on the shortcut menu


I have other Pivot tables that have more headings that the one I am having
trouble with yet I don’t get the same error message with these.

Even why I try creating a pivot table from scratch I get the same message if
I attempt to add the extra heading in the Row field.

Can anyone help?


Thanks

John

 

[Debra Dalgleish]

The limitations for pivot tables in Excel 2000 are much lower than those
later version of Excel.

This MSKB article outlines the limits:

2000 -- Limits of PivotTables in Excel
http://support.microsoft.com/kb/211517/

If you have lots of unique values in your row fields, they'll quickly
multiply to a number that's higher than the row item limit.

 

[John Calder]

Debra

Thanks for your prompt response. I have 1200 rows of data and each one of
these would be considered unique as the 1st 6 columns of each row are linked
to another spreadsheet, however my understanding is that up to 8,000 unique
records are possible in Excel 2000. As I have only a maximum 1200 I would
have thought that this would not be a problem. I even tried cutting the raw
data back to 600 rows and I still get the error meassge.

Is there anyway to overcome this?

Thanks

 

[Debra Dalgleish]

The 8000 unique item limit is per field. You only have 1200 records, so
won't hit that limit.

In the article I mentioned: http://support.microsoft.com/kb/211517/
it describes how the row and column limits are calculated.
In all the row fields, the product of the items is about 2 billion (2^31)

In your pivot table, row field 1 might have 400 unique items, row field
2 might have 300 unique items and row field 3 might have 200 items.
The product is 400*300*200= 24,000,000
Then, if you try to add another row field, that has 100 unique items,
the product is 400*300*200*100= 2,400,000,000
which would be over the 2 billion limit.

 

[John Calder]

Debra

Thanks very much for your explanation. I work for a a large organisation
which uses Excel 2000, and, as I was creating this pivot table to go on a web
page so that others could use it, I will need to look at alternatives.

Once again, thanks for you help Debra


John