PivotTable Based on large data

[A.M]

Hi,

Using Excel 2000, we are trying to have PivotTable based on 130,000 rows of
data.
The problem is Excel has 65,000 row limitation. And I prefer to have
PivotTable based og excel tablerather than OLAP cube.
Two solution comes to my mind and I am wodering how practical are they:

Solution 1) Upgrade to excel 2003, Because I am expecting excel 2003 have
row capacity more than 65,000 rows. Am i correct?

Solution 2) Have data in two different sheets, But i am not sure that we can
have PivotTable based on Fragmented data in two sheets. Can I ?

Could anyone tell me how practical are my two solutions ?

Any help would be apprecited,
Ali

 

[Frank Kabel]

Hi Ali,

Using Excel 2000, we are trying to have PivotTable based on 130,000
rows of data.
The problem is Excel has 65,000 row limitation. And I prefer to have
PivotTable based og excel tablerather than OLAP cube.
Two solution comes to my mind and I am wodering how practical are
they:
Solution 1) Upgrade to excel 2003, Because I am expecting excel 2003
have row capacity more than 65,000 rows. Am i correct?

No, won't help. Excel 2003 still has the 65536 row limit

Solution 2) Have data in two different sheets, But i am not sure that
we can have PivotTable based on Fragmented data in two sheets. Can I

?
AFAIK this is not possible.

Though I have never tried that maybe choose a database (MS Access) as
datasource for your pivot table. This should do the trick.

Frank

 

[Eric]

Ali,

I had this very issue with a large database. I, too,
wanted the pivot table functionality but Excel couldn't
handle the amount of data I had.

Instead, I put the raw data into Microsoft Access. Then,
when creating the pivot table, you select the "External
Data" functionality. Then you can create a pivot table
using the Microsoft Access data BUT using the fuctionality
of a pivot table.

Unless your data is simply too complex, this has worked
for me on several occasions.

I hope that helps!

Eric

 

[A.M]

Thanks Eric, That was good idea.

Ali