Hi there,

I have a workbook with pivot table fetching data from a dynamic data range, file size ~2.5 mb.

Everytime after updating the raw data and refresh the pivot table, the file size increases to ~7mb.

However, if i saved the workbook, closed and reopened, and saved again, the file size will be back to normal ~2.5mb.

I have used macro to clear the pivot cache before closing/saving the workbooko but it doesn't help.

Also, I ran a macro to check the number of pivot cache after updating and it show that there's no multiple cache. (only 1).

Thus, it might not be due to the pivot cache, but I have no idea what are the other reasons.

Does anyone face the same issue here?

Really appreciate if anyonce could assist.

Thank you very much!!

Regards,

HC

Hi HC,

Do you mind telling where did you store the dynamic data? In the same workbook, Access or SQL?

I have tested with my local environment (Office 2013), use both of Excel and Access as external data source. But I can't reproduce this issue.

Please try the following steps and check if they are helpful.

First check to see if there is excess formatting in the worksheet(s).
On each worksheet do Ctrl+End.
If the cell selected is way beyond the end of the data, select the entire rows between the actual end of the data and the Ctrl+End cell and delete them.  Similarly delete entire columns between the actual end of the data and the Ctrl+End cell.  Then save the workbook and check its size.

If that's not it, perhaps you have queried a large amount of external data to feed a pivot table.
By default that will be cached in a hidden data structure within the workbook and can increase its size.
I don't know which version of Excel you are using (always helps if you say!). For 2010, right-click the pivot table > Pivot table options > Data > uncheck save source data with file.

Hope it's helpful.

Regards,

George Zhao
TechNet Community Support

Hi George,

Thank you for your reply.

I am using Excel 2010.

The source data is in CSV format. I import the data to my workbook and the data would be stored in the same workbook.

I've tried the Ctrl+End method, however, nope, I don't have unused cell in each of my sheets.

I would agree with you that a large amount of external data is queried from my CSV to feed a pivot table.

Currenty my raw data is up to 78619 row with 9 columns. I am updating this workbook on a weekly basis, that means every round of update would be around 3400 new rows added.

I've tried the uncheck save source data with file option, but to no avail.

It seems like the cache is hidden somewhere after updating and can only be cleared after I reopen the workbook again. It's just like it will be there for this "session", thus you have to close the workbook ("session") and reopen again (reopen the "session").

It's not really a big deal, however, it is just annoying that I have to do the save-close-open-save routine everytime after updating the workbook. Just hoping that I would be able to identify the root cause and get rid of it once and for all.

Thank you for your assitance though.

Regards,

HC

• Edited by HC_YHC 11 minutes ago Typo