Workbook Size Blow-out and Phantom Links

Torbjorn Zetterlund ThunderBear
0

Unfortunately it is not too uncommon to have an Excel Workbook increase in file size for no apparent reason. Below are some methods to overcome this problem, that should be performed in the order stated! Please note OzGrid accepts no responsibility for any loss of data. This means  save a copy of your Workbook first. After each step  Saveand check the File size by going to  File>Properties/General  If it has reduced the file size you shouldn’t need to go any further!

Avoid saving as multiple versions.  Save as   Microsoft Excel Workbook (*.xls). You can make this the default by going to Tools>Options/Transition and selecting  Microsoft Excel Workbook  from the  Save Excel files as  Combobox. This alone can reduce file size by  50%

Do the following to  ALL Worksheets. Select any single cell then push  F5, click  Special  and check the Blanks option and click  Ok. Now go to  Edit>Clear>All. Manually go to the last used row in the Worksheet (do not use  Edit>Go to-Special   Last Cell ). Select the entire row beneath, then holding down the  Ctrl+Shift  key push the  Down Arrow.  This should select all unused rows. Now go to  Edit>Clear>All. Do the same for the Columns. Now  Save.

Right click on any sheet name tab and select Select all sheets . Now right click again and select Move or copy Check the  Create a copy  option then choose (New Book) and clickOk. Save this new Workbook as any name and check it’s file size via  File>Properties/General.  

If no dramatic difference delete one sheet at a time and  Save, each time check it’s file size via  File>Properties/General  and see if there has been a disproportional reduction in file size.

If there  has, the Worksheet may have been corrupt, go back the the original Workbook and copy the entire contents of the possibly corrupt Worksheet. Come back to the new Workbook, insert a  new  Worksheet and paste the data onto it. If this does not help you may need to delete a few rows at the time (Saving each time) until you find the offending row(s).

If you have Modules and/or UserForms in the Workbook, open the  VBE  (Alt+F11) and right click on each in the Project Explorer (Ctrl+R) and  Export. Now, in a  newWorkbook again open the VBE and go to  File>Import File..  and import all Modules and UserForms into this Workbook. Use the  Move or copy  method (as shown in step above) to copy all sheets into this Workbook  Save.

Phantom Links

See  Find Excel Links  to help find & repair Excel links.

Another quite common problem with Excel is that at times your Workbook may be asking you to update links when you do not have any. Of course the very first thing to check is that there are not any links you do not know about, which steps  1  and  2  will do.

1.Unhide any hidden Worksheets first. Then right click on any sheet name tab and select Select all sheets . Now go to  Edit>Find  and in the  Find What:  box type:  [*]  also selectFormulas  from the  Look in:  box and click  Find Next. This will find any formulae referring to another Workbook.

2.Go to  Insert>Name>Define  and scroll through the list to ensure no named ranges are referring to another Workbook.

3.Go to each sheet and ensure you have no linked Pictures, Charts or Controls.

4.Go to  Edit>Links  and select the file name under  Source file, now click  Change Source…  In the Change Links dialog locate the Active workbook, ie the one you are in now, select it and click  Ok. This will force the Link to refer  back to itself. If you get an  error  go to step 5!

5.Open a new Workbook, save it as any name. Create a  real  link to it from you problem Workbook. Now go to  Edit>Links  and select the file name (not the newly created file!) under  Source file,  again click the  Change Source…  button but this time select the  newly created  Workbook from the Change Links dialog and click  Ok. Now Save the Workbook and delete the real link you created and save

Last but not least go here:  http://support.microsoft.com/  and download the:  Download Dellinks.exe now

Leave a Reply