Reduce size of spreadsheet

[m9m9m9m]

Hi there,

I use excel 2003, and have a 15MB s/s. A colleague of mine pasted in a pivot table from a previous version of the same s/s bloating it to 21MB (the "save data with table layout" option was ticked by mistake). Removing the pivot table has reduced the s/s to 18MB - but not to 15MB. So despite the pivot table deleted there is still some 3MB of data stored somewhere (pivot table cache perhaps not properly deleted ?). As this s/s is a 2 year old working s/s I suspect there is more orphan data that has accumulated in it this way.

Apart from copying all the source data into a brand new s/s and re-creating all formulas, formats tables - any idea how I can shrink the table back to 15MB ? Perhaps a macro to delete all stored data ?

Thanks M9

 

[hippiehacker]

how have you deleted the contents with delete button?
the delete button as far as I remember has the functionality of clear contents and not properly deleting the used cell space I've seen this mistake from people very often
you may want to select all empty rows/columns and use instead of the delete button right mouse menu and delete or from the excel menu/ribbon delete

 

[PaddyD]

Bunch of things to do:

Excel Calculation Bottlenecks - Decision Models

..scroll down to size & memory issues.

 

[m9m9m9m]

Thanks for that link. Lots to read and try.

 

[m9m9m9m]

I deleted it using the delete key (no ribbons in Excel 2003). Is there an known issue with it ?

 

[hippiehacker]

i tell in a basic excel course my trainees always the following (might be wrong so don't shout at me)

•Normally when working in Excel, if we want to remove a cell(s) contents we would simply push the Delete key on our keyboard. This would delete the contents of the cell. However, it will not delete the formatting of the cell in any way. What this means is, if we had a cell with a yellow background, blue font and formatted for currency, and the value $10. 00 in the cell. Pushing Delete would only remove the value 10. All the other cell attributes would remain intact.
•Another method is to use Edit à Clear with the following attributes
–All – will delete any cell attributes as well as formatting
–Formats – deletes only the formatting
–Contents – deletes only contents (describes above)
–Comments – deletes any comments assigned to a Cell

 

[hippiehacker]

i tell in a basic excel course my trainees always the following (might be wrong so don't shout at me)

•Normally when working in Excel, if we want to remove a cell(s) contents we would simply push the Delete key on our keyboard. This would delete the contents of the cell. However, it will not delete the formatting of the cell in any way. What this means is, if we had a cell with a yellow background, blue font and formatted for currency, and the value $10. 00 in the cell. Pushing Delete would only remove the value 10. All the other cell attributes would remain intact.
•Another method is to use Edit à Clear with the following attributes
–All – will delete any cell attributes as well as formatting
–Formats – deletes only the formatting
–Contents – deletes only contents (describes above)
–Comments – deletes any comments assigned to a Cell