How can I reduce the file size of an Excel Spreadsheet? 1

[gk759]

Hi!,

I have an excel spreadsheet that has fomulas and data on one sheet. Its file size is 28MB, from Column GJ to Row 6267.I know that excel works optimally when its less than 15 MB file size.

I tried breaking it into sections and pasting the information on seperate sheets. Now my problem is how do I update all the formulas to reference the other sheets as they are looking for cells on the same sheet.

Is this the right method to reduce the files size?,

your sugestions are welcome,


Thanks,

Gary

 

[Dreamboat]

See my FAQ here on Excel workbook corruption, copy the sheets to a new workbook.


Also, select all unused columns and hit Edit-Clear-All. Do the same for all unused rows.

http://www.TheWordExpert.com

 

[DaleWatson123321]

Gary,

If you copy or move data from one sheet to another "within" the SAME file, one should not expect the size of the file to change. Instead, you should move data to separate workbooks.

If you still have a copy of your original file, here's what you should do:

1) Open the "original" file (workbook), and while you have this opened,

2) Create a NEW workbook.

3) Use <Control> <Tab> to go to your original workbook.

4) Highlight a section of your original workbook that you want to move to your NEW workbook.

4) Use &quot;Move&quot; - <Control> X.

5) Use <Control> <Tab> to go to your NEW workbook.

6) Go to the area where you want to paste this data, and use Paste - <Control> V.

7) Repeat steps 1-6 for as much data as you want to move from your original workbook to the NEW workbook.

8) Save the NEW workbook - under an appropriate name - which &quot;associates&quot; it with the &quot;original&quot; workbook, and of course you should preferably save it within the same folder.

By &quot;moving&quot; the data, any formulas in your original workbook which refer to the data you have moved will automatically change to reflect the reference to the NEW workbook.

To test out your 2 workbooks, a) save both files, b) make a change to one of cells in your NEW workbook - to a cell referenced by a formula in your &quot;original&quot; workbook. c) save the NEW file. d) close the NEW file. e) open the &quot;original&quot; file, and you should see that WITHOUT opening the NEW file, your formula will automatically reflect the change you made to the NEW file.

To break down your &quot;original&quot; file further, repeat Steps 1 - 8. You should preferably continue to use a file naming convention that associates all of your (linked) workbooks with the &quot;original&quot; workbook. For example, if your &quot;original&quot; workbook is named &quot;Expenses&quot;, then perhaps you would save your other workbooks with names like &quot;Expenses_input_01&quot;, &quot;Expenses_input_02&quot;, etc.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[gk759]

Dale ,

i treid using your method, I keep getting access violation ,Dr.Watson error. I selected the cells and followed ur instructions to paste it another work book within the same directory.

What should I do next?


Gary

 

[gk759]

Dreamboat,


I tried your tips too, but the file size didnt change for some reason.


Please let me know if there is anything else thats possible,

Thanks,

Gary

 

[gk759]

Dale,

Im sorry it not an access violation error., it an application error , excel.exe displayed by Dr.Watson for Windows NT.


Looking forward to your reply,

Gary

 

[DaleWatson123321]

Gary,

Sorry to hear that your efforts have not worked.

The testing I did was of course on much smaller workbooks, and I never encountered any problems.

However, in dealing with a MUCH larger workbook, and given Excel's known problems with LARGE workbooks, it might be that you've &quot;already&quot; reached that point where such problems will &quot;get in the way&quot; of fixing the situation as I proposed.

There are basically TWO options I can think of:

1) To increase MEMORY, especially given that memory today is fairly cheap, and see if that makes a difference.

If you have a co-worker with more memory, perhaps you could ask that you &quot;work on each others' PC&quot; long enough for you to test your file.

2) I suggest you attempt to &quot;break apart&quot; your large file &quot;one small chunk at at time&quot;. If you can get it to work with the first &quot;small chunk&quot;, then this should confirm that the process will work - by gradually separating off additional parts of the original file.

If this &quot;small chunk process&quot; works, you should probably proceed on the basis of &quot;exiting&quot; or &quot;closing out&quot; of the files - each step of the way - to permit Excel to &quot;re-gain&quot; the memory saved from removal of each chunk of data.

I hope this works, and that your file is NOT somehow already &quot;past the point of no return&quot; in terms of having already caused &quot;internal&quot; problems.

Good Luck. Please keep both Dreamboat and I &quot;posted&quot; as to your progress. Dreamboat has much more experience than I in dealing with &quot;internal type errors&quot;, so hopefully she can suggest an alternative if my suggestions don't work out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

What happens when you winzip it? If you can get it to, say, less than 5 MB, I'll take a look at it.

http://www.TheWordExpert.com

 

[gk759]

Thanks Dale and Dream boat,

I have overcome my problem.We had saved the files of the spreadsheet every month. So we reverted to an older copy of the spreadsheet and substituted the data.

It seems to be working fine now.Because the old file had a file size of 10.5 MB only and grew overtime.

Thank you once again for your help,

Take care,

Gary