Pivot Table file size

[tango91a]

Hi,

I have a pivot table that obtains data from an Access database on a
quarterly basis. As at Q2 the pivot table file size was 20MB after retrieving
100,000 records from the database.
As at Q3 the pivot table file size was 75MB after retrieving 140,000 records
from the same database. Nothing has changed regarding the number of columns
etc. Just a refresh for updated data.
For the previous year: 50MB file for 250,000 records. Same database.

Any reason for the dramatic increase in file size for Q3?
Is this somehow related to the pivot table cache or memory? If so, how do I
fix this. I cleared the cache via a VBA macro, but this did not change
anything.

BTW I use Excel 2003.
Thanks!!

 

[Jim Thomlinson]

Most likely one of more of the sheets has cells that are empty but are
actually taking up memory (initialized but no longer used). Do the scroll
bars on one or more of the sheets allow you to drag all the way down to row
65,535??? to clean up the memory delete all rows below the last row of data
and delete all columns to the right of the last column with data. Now save
the file and the memory should decrease. Pivot tables are notorious for doing
this.

 

[tango91a]

Thanks for the response. I tried but it did not change the file size.

Any other ideas....anyone?

 

[nboisen1]

I assume that you are bring the actual TEXT data (and not just primary
keys) from Access into the Pivot Table.
Therefore, the size of the file will be dependent upon the length of
the text in each cell (unlike numeric data which is of constant size).
Is it possible that in this years Q2 data there are many records with
substantially longer text?

 

[tango91a]

The subsequent quarters do not have longer text or numbers in the fields.

This issue still puzzles me. Q4 updates to the pivot table created the same
issue. For instance, only 2 additional journals (4 line jrnls) were posted on
Friday to change a number in one account. However when the pivot table was
refreshed on Monday, the file size increased from 70MB to 120MB. There must
be something in Excel that is creating this issue.

Thanks all for your responses and help.