I have a workbook with one worksheet and a bunch of macros. I pull in a range of data from another sheet and work with it-- it can have hundreds of rows, columns out to column BE. I do a lot of conditional formatting of the data via the macros. I've been noticing that over time, the file gets larger. I have a cleanup routine to remove the old data and clear out the conditional formatting on the sheet, but I read somewhere that you also have to clear contents on all the rows and columns outside of your normal work area. So I've added that to my clean up routine. Is there anything else I should be doing to keep the file at a reasonable size? The workbook is now about 4MB, but it has been up to 35MB !
Spreadsheet size increases over time.
- Thread starter glennpc
- Start date
Chihiro
Excel Ninja
One thing to note. If you clear Pivot Cache, you will lose majority of Pivot Table benefit (drilldown, etc). Rather flush out unnecessary ones and consolidate duplicates.
If you are going to add multiple Pivot table from same data table, make sure to copy Pivot Table and not create each time (this will ensure Pivot Cache isn't duplicated).
Another way to keep the file size down, is to have source data on separate workbook from Pivot Table (you can only refresh when it's on same network, but can still drilldown regardless).
There are few codes out there that will consolidate duplicate Pivot Cache. One example link below.
http://ramblings.mcpher.com/Home/excelquirks/snippets/pivotcache
If you are going to add multiple Pivot table from same data table, make sure to copy Pivot Table and not create each time (this will ensure Pivot Cache isn't duplicated).
Another way to keep the file size down, is to have source data on separate workbook from Pivot Table (you can only refresh when it's on same network, but can still drilldown regardless).
There are few codes out there that will consolidate duplicate Pivot Cache. One example link below.
http://ramblings.mcpher.com/Home/excelquirks/snippets/pivotcache