Limited Pivot Tbl Row and Column Fields

[LPS]

Usign Excel 2000 on an XP box:
I have a client who wants to create a Pivot Table using a spreasheet with
approx. 1500 rows of data and 8 columns. When she creates the Pivot Table,
she gets an error message saying that she is trying to add too many rows
and/or columns. The Pivot Table contains one Page Field, One Column Field,
one Data field and will only allow her 3 Row fields. When she tries to add
the 4th Row Field, she gets the error telling her she is trying to add too
many fields. According to the Excel Help Specifications sheet, the number of
Row and Column fields for Pivot Tables is limited by available memory. Her
PC has 1 GB of RAM and the spreadsheet's file size is just over 1 MB. No
other applications are open. Can anyone explain why she is getting this
error, and if there is a way to work around it.
All help is greatly appreciated -Thx.
LPS

 

[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.

For example, in your client's 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 row item limit.

 

[LPS]

Thank you very much, Debra. Your explanation is very clear and I have read
the article behind the link you provided. I have a stupid question. I am
very "rusty" with the mathematical principles behind Pivot Tables. Can you
tell me why the row and/or column items are multiplied instead of added?

Thank you so much,
LPS

 

[Debra Dalgleish]

The theory is that each item in each row field could potentially appear
with each item in every other row field. To calculate the total number
of possible combinations, the item numbers are multiplied.

For example, you might have a list of orders for hammers, wrenches, and
chisels, in either professional or utility grade, with grips in black,
red, yellow or blue.

The pivot table would calculate that there are 3*2*4 = 24 possible
combinations of those tools and options.

Even if hammers are only sold with black grips, the calculation would be
based on all four colours. In Excel 2000, potential combinations are
calculated, not actual ones.

 

[LPS]

Hi again Debra. I appreciate your explanation (and expertise) and it all
makes sense again.

Thank you,