Excel file size - what is suitable/workable? 3

[Doodstaa]

Hi - please can somebody advise - I have a user who has asked what is a workable file size limit for an excel file, (before it becomes unworkable as it cannot be loaded into memory) - The user has a 40mb spreadsheet she continually works on - we have continually advised that this is too large but she won't accept this answer. Thanks in advance for any help.
Steve

 

[Zorlod]

Thats a large file!! I dont know numbers as far as file size, but i've found that excel works better if you split the data up over several sheets intstead of just one. Also, if you have a large image on every page, such as a company logo, you can take that off and it may help with the file size. I find it hard to believe that you can have a 40mb file that is all data. There must be something to clean out of there.

 

[SimonDavis]

Yup - that's too big. (Alright Chav?)

I'd have a look at things like formatting - if you put a bold line or cell borders for example across or down the whole sheet (by clicking the column or row number instead of just the cells you want to format) the sheet could become 65,000 by 256 cells, even if you've only got data in part of that area.

Same goes for formulas - if you fill down the whole column instead of just where the data is, the sheet will get bloated.

I'd suggest taking a copy of the sheet, highlight the 'empty' areas below and to the right of the actual data, and delete the contents. Try saving it and check the size again. Don't change the original until you're sure it still works!

 

[Store300]

Another cause of large increases in size is naming ranges within the book, the more you have the bigger it gets.

 

[Dreamboat]

Doodsta, click on the link to take you back to the list of questions here. Then, click on the FAQ tab. Read my FAQ on pre-corruption cleanup for Excel files. Tell her to take the steps:

http://www.tek-tips.com/faqs.cfm?spid=68&sfid=619&CFID=7345857&CFTOKEN=48329016

techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Doodstaa]

Thanks to all for responses - v useful to know - the user however is insistent that she needs to know the maximum size an excel file should be - I could say to her 'how long is a piece of string' however I know she would not be satisfied with that as an answer - please can anybody tell me if there is an absolute maximum size for excel files and if so what is it?
Thanks in advance for any help
Steve

 

[Dreamboat]

Go to Excel help. Type "limits" without the quotes and click on Excel specifications.

To my knowledge, there is no max electronic size reference provided by MS.

Please tell her, however, that she is heading for trouble and she'd better have a backup.

(Just slap her, Doodstaa. LOL )

techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Zelandakh]

Tell her (and it is vaguely true) that if she only opens ONE excel spreadsheet then its maximum size should be as follows:

Total machine RAM
minus
16MB for 95, 24MB for 98, 32MB for NT4 and 64MB for 2000.
minus
16MB for Outlook running
minus
8MB for every other MS product running

This usually keeps them quiet and comes up with a small (or negative number).

4MB is a respectable spreadsheet. 10MB will run slowly and will become unstable eventually.
Any file over 16MB is unstable and should be split into components (like data and reports or just split into sheets).

Hope this pack of lies helps

 

[SimonDavis]

I'd grass her up to the Network dept - I bet they'd love to hear about somebody strangling their network with 40mb files.

For your info, a very unscientific observation. The biggest excel file I have is 289k. This has 10 sheets, all of which are 124 columns wide, by about 80 rows. To get to 40mb, my sheet would contain over 1,300 pages.

Anybody know how big a 65,000 x 256 cell sheet is? I tried and locked Excel. Still be it's not 40mb.

 

[luceze]

I don't know if anyone cares any more but I copied the number 1 in every cell with no formatting in an excel worksheet and saved it. The file size was 98.8 MB. Also, when open it maxed out all my ram and slowed the workstation to a crawl. (p600, 256MB ram)

 

[JrClown]

Is it DATA that you are storing in this Excel file?? Why not simply use Access and provide her with a Active Server Page front end?
Screw that 40 meg Excel file. "The reward of one duty done is the power to fulfill another"
<%
Jr Clown
%>

 

[Doodstaa]

Thanks again to everyone for comments - JrClown - I have advised the user approx 7 or 8 times that this data really belongs in an Access dbse - but she just doesn't want to know. The call is closed now as she has been told she is going to have to do Excel training, on an on-line training facility we have. (!!!).
Personally, I've found all this info v useful, - so thanks!
Steve