Excel file too large

mikebaz

New Member
Joined
Oct 29, 2002
Messages
34
Hi,

I have an excel file that is very large 38mb and it takes a long time to open on my very slow woek pc, can this file be compressed or something without losing data?

Thanks
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Just a few thoughts to speed up:
Have you deleted columns/rows that contain no data. Press ctrl-end on each sheet to see if you have blank rows/columns. If so delete them (don't just clear them). When you save, your file should be smaller.

Have you considered splitting it into separate (linked) files?

Have you reviewed the formulas to see if data from a number of columns/rows can be condensed into one?

Is your calculation set to manual?
 

mikebaz

New Member
Joined
Oct 29, 2002
Messages
34
The file is from another dept. and I have yet to see it's format due to the length of time it takes to open, too impatient! But once I get it open I will be sure to check those items and see if I can do anything with them.

Thanks!
 

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920

ADVERTISEMENT

In addtion to what already have been mention another good idea is to rebuild the workbook with a tool such as Excel Workbook Rebuilder by Andrew Baker. It's not free but it may be a small cost compared to to it manually.

Check it out here:
http://www.vbusers.com/commercial/Rebuild.asp

and You can also download an evaluation version of it.

(No, I have no commercial interest in it - I´m only a pleased customer :wink: )

Good luck!
Dennis
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
From experience, three of the main contributors to file size bloat:
1. Saving the file in a lower version.
2. Embedded graphics.
3. The actual used range far exceeds the visible used range.

Some of the following may be relevant;

http://cpearson.com/excel/optimize.htm

Some tips on overcoming slow response (David McRitchie)
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Opening/Saving Bottlenecks (Charles Williams)
http://www.decisionmodels.com/optspeedd.htm#OpSave
http://www.decisionmodels.com/optspeed.htm

Optimizing for Size and Speed
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/web/013.asp

Random collection of various statements/comments from people on the Net (I collect these from time to time):

Charles Williams
www.DecisionModels.com

- make the range referred to in Vlookup/Index as small as possible
- make sure all your named ranges and all other ranges refer to as small a range of cells as possible
- make sure your used range is not excessive ( check with edit -->goto-->Special-->Lastcell)
- eliminate as much sparseness as possible (ie as few blank cells embedded in the used range as possible)
- use dynamic ranges if appropriate
- store frequently occurring "formula snippets" in named formulas
- use multi-cell array formulae to replace blocks of formulae where possible
- remove zero-sized objects
- switch off Change tracking for shared workbooks
- reduce the number of worksheets by merging several sheets into one
- Avoid references to large ranges on other worksheets, particularly references to blank or unused cells
- simplify formatting/reduce fonts etc
- avoid embedded graphics
- small savings are possible with short wide sheets rather than tall narrow sheets ( but less useable).

Chip Pearson

Set the calculation mode to manual unless you really need it set to automatic while your code executes.

Set Application.ScreenUpdating to False to prevent Excel from updating the screen while your code runs.

Make use of the With / End With construct whenever possible.

Avoid Variant and Object type variables whenever possible - declare variables with a specific type.

Don 't use the Select method and Selection object -- access a range
directly. E.g., instead of
Range("A1").Select
Selection.Value = 123
use
Range("A1").Value = 123

Sundry

You could put your macro's in a separate file and reduce file size that way.

Make sure that with conditional formatting you format only the range you want and not the whole row, column or sheet.

Don’t copy and paste images from Word into Excel - use Insert/Picture

Try reducing the amount of formatting you apply with the macro.

Try commenting out all the VBA code, save.

Try separating out the macro from the workbook you are actually having the macro work on (make them two separate workbooks).

Over time if you use the same workbook over and over, applying values, formats, copying worksheets, garbage will collect bloating the file size.

Macro Modules

You should keep macro modules at less than 64K in size. How do you tell the size - export the module and then look at the .bas file to see what the file size is.
Tom Ogilvy (Microsoft MVP)

The module size limitation isn't hard and fast, nor is it limited to any specific version of Excel. In fact I don't think it's ever been officially documented.However , it 's pretty well recognized among professional Excel developers that if you create code modules larger than 64KB you will very likely run into problems.
Rob Bovey, MCSE, MCSD, Excel MVP


HTH

Mike
 

Jeremyoos

New Member
Joined
Feb 19, 2008
Messages
3
Just a few thoughts to speed up:
Have you deleted columns/rows that contain no data. Press ctrl-end on each sheet to see if you have blank rows/columns. If so delete them (don't just clear them). When you save, your file should be smaller.

Have you considered splitting it into separate (linked) files?

Have you reviewed the formulas to see if data from a number of columns/rows can be condensed into one?

Is your calculation set to manual?

The first tip worked great for me, thanks. File size went from > 17 Megs to 120K. Dunno how it got to that size in the first place.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,891
Messages
5,766,966
Members
425,391
Latest member
Alono23

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
Top