Compressing Workbook

Greta

New Member
Joined
Oct 19, 2002
Messages
19
How do you compress an excel file, such that compressing the file reduces the file size.

Any help.

Regards
Greta
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Greta,

It depends on what is in your workbook and whether you want to use other applications or not.

If just want a way of making the existing file smaller (for sending by email for example) then consider using file compression utility. There are many available, a common example is WinZip.

If you just want to 'slim down' your workbook without using an external application have a look at this info from Dave Hawley's site:

Unusual File Size Increase

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 Save and 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 click Ok. 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 new Workbook 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

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 select Formulas 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/default.aspx?scid=kb;EN-US;q188449 and download the: Download Dellinks.exe now


HTH

EDIT: Oops - poor formatting!
_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-11-01 04:19
 
Upvote 0
Hi Richie(UK)

Sounds like Dave Hawley has a fan

I never liked the guy, he’s rubbish at Excel and knows little of VBA.

Any way he’s British!!! So that’s reason enough, or is it maybe he’s from my part of town???


Dave as you know is Jacks buddy, glad you use his site and can I suggest pick up his posts and view all his posts, some GURU post for sure

Im sad to say – Dave loves it but he’s in Western Australia now days not good old South London a place he knows well


I only ever tip my hat to Dave – yes I get agro over this but I don’t care, find a better programmer I say in Excel – but he also does many languages as is just as good

Take care Richie(uk) ,many of my snips you will see are Daves and based around his programming style, funny that eh!!

Your friend in South London

Jack
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top