Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table file size
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table file size
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. -- HTH... Jim Thomlinson "tango91a" wrote: 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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table file size
Thanks for the response. I tried but it did not change the file size.
Any other ideas....anyone? "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "tango91a" wrote: 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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table file size
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table file size
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. " wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I shrink a file size w/ a pivot table and many formulas? | Excel Worksheet Functions | |||
Pivot table has bloted file size - how do I bring it down | Excel Discussion (Misc queries) | |||
reduce size of pivot table file | Excel Discussion (Misc queries) | |||
pivot table cause file size to grow | Excel Discussion (Misc queries) | |||
Pivot table - size of file? | Excel Discussion (Misc queries) |