Too many unique rows in a pivot-table
Posted by Harry Pollard on October 09, 2001 2:12 AM
Does anyone know the maximum number of unique rows of data you can use in an Excel pivot-table.
I am getting an error message when I use a table with 22689 rows of which 8688 are unique.
I can find no reference to the maximum in the help files.
I am using Excel 97 SR-2 (h)
Is there any work-around ?.
Posted by Eric on October 09, 2001 3:25 AM
Check under "pivot table report specifications" in help file, limit (for Excel 2000) is 8000 rows of output (NT)
Posted by Eric on October 09, 2001 3:55 AM
missed that last line about a workaround :/
If the data on which you were pivoting was both numeric and resulted in I guess three different tactics would be:
a) "simplify/chunk" the data that is giving you more than 8000 rows of output.
b) eschew the pivot table all together and use array (CSE) and/or sumif/countif formulae to generate the same information. I'm not sure what 8,269 array formulas would do to your machine's performance, so be careful! I think array-based formulae are slower than pivot-table based calculations, but do not have the 8000 row limit.
c) pivot on subsets of the list
If you posted some sample data, especially of the column generating the 8000+ unique rows, you could probably get more targeted advice.
HTH
Posted by Harry on October 09, 2001 6:30 AM
Thanks for the replies.
I have verified that the maximum number of unique rows is 8000.
