Excel runs slowly on computer

[SacreBleu]

Frequently, my Excell workbooks get very large because of added worksheets and data, and then they run very sluggishly on the computer.
What is the best way to upgrade a computer to solve this? More RAM, faster processor chip, both, or what?

 

[GregLocock]

Fire up your performance monitor or task manager and check your RAM and virtual memory usage. Or listen for disk thrashing.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SacreBleu]

Excel hardly causes any disk activity at all. It must be either processor, RAM or both - I was wondering if anyone had the same experience.

 

[GregLocock]

If you aren't using virtual memory then RAM isn't the problem. So it must be the CPU, I guess.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[johnwm]

What processor?
How much RAM?
What OS?
How many other processes running?
What version of Excel?
How big are your Excel files?

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[SacreBleu]

johwm,
I am referring to 4 different computers.
Excel 97 (I have no use for the fuzzy icons and other sillinesss associated with Excel 2003)

RAM varies from 256K to 1G

Operating systems - Win98SE, Win2000, XP Pro

CPU- Athlon 1700+, Pentium 3 GHz, more or less.

The "slow" machine- Win98SE+Athlon1700+256K is almost as fast as the more recent ones.

I am not that savvy about other processes running. The more recent machines have many running, most of which are a mystery to me.

The Excell becomes sluggish at about 20Mb size.

 

[johnwm]

If your spreadsheet is around 20Mb you may need to consider redesign, particularly as you're running out-of-date software.

1. If your spreadsheet contains mainly data with some lookups and simple calcs, then a database program may work better.

2. If it's mainly calculation then consider splitting the spreadsheet into separate functions

3. If it's truly monolithic (all calculations, all inter-related) then such an old version of Excel may not be the tool for the job.

It sounds from your first post <q> because of added worksheets and data </q> that a major overhaul of the design may be in order.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[GregLocock]

I wouldn't like to have to revalidate a 20 Mb spreadsheet after a redesign.

To be honest I'm moving away from spreadsheets for anything that has to be maintainable and is safety related.



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SacreBleu]

johnwm,
It has a moderate amount of data tables, lookup, match, offset functions. It has a very large amount of formulae, with a lot of "branch points" (not really savvy on the lingo here). There are no macros, nor VBA involved. Minimal graphics. I don't understand what you mean, "split into separate functions".
It does the complete design of a wood framed building. When, for example, there are more shear wall designs than it is set up for, one simply copy/pastes new worksheets into the workbook.
I don't have the time or the mental fortitude to learn Database. Just keeping up with our Building Code is a challenge.
I will consider upgrading to a later version of Excel. I didn't know that could be an improvement.

Greg:
It is in a constant state of being upgraded, with more features. That is not an easy task.

 

[electricpete]

If one of your computers has only 256k ram, I think it's time to upgrade ;-)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[EGT01]

SacreBleu,

Maybe I've lived a sheltered life but a 20mb Excel file seems really big. Are you comfortable with knowing what you have in the file justifies its size? I've known that Excel files can get corrupted causing bloated file sizes and still be able to open.

http://www.annoyances.org/exec/forum/win98/1034276755

Just something to think about if you haven't already. Is your target workbook file size increasing proportionately to the size of the worksheets you are adding?

 

[SacreBleu]

EGT01,
Sometimes , the project calculation workbook can become about 20mb in size. Typically, it is more in the range of 6-10mb.

I have tried the Save As (under different file name). That does not seem to have any effect.

This workbook is continually "tweaked". I have been able to clean it up on occasion by carefully copy/paste just the occupied cells of a worksheet onto a blank worksheet.

Another thing that helps is making sure the "Control-End" last cell is really the last cell of data in the worksheet.

Admittedly, since I am not an Excel expert, there probably is a lot of inefficiency (redundancy?) inherent in the workbook formulae, data, etc.

Electricpete:
The machine with 256K runs Win98SE, and is not connected to the Internet. It rarely hangs or crashes, so I figured it is doing just fine...and it seems only a little slower than the 3 other machines I use.

 

[electricpete]

256K!!!????!!!! (emphasis on the K)

Come on now, think about it. The early 8088 PC's had 512K ram if memory serves me right.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Sorry, I couldn't resist a little dig there. I'm sure you meant 256 Meg.

On the subject of making your file smaller:

1 - check out thread770-80600

2 - As johnwm said, consider breaking your file into smaller pieces. You can still have a one-way lookup to get data from a file that is closed on the disk (use filename and "!" before the cell reference). Also you can provide a hyperlink in on file to open another excel file. I store data on 30 different machines in 30 different spreadsheets. I have one master spreadsheet that extracts summary data from each of the 30 and displays it along with a hyperlink to open the individual file for more details. It may not be applicable to your situation but it helped me keep my files manageable.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[jghrist]

I'd agree with Greg Locock that very large spreadsheets are not suitable for safety related designs, such as the complete design of a wood-frame building. There are just too many places where errors can creep in and are invisible without a comprehensive review. Very good documentation and built-in checks can help, but something like Mathcad is much more easily documented and checked for errors.

 

[SacreBleu]

electricpete,
You found me out - I wondered if I had some form of dyslexia, or it was just a "grammatical" mistake!

 

[IRstuff]

actually, I think the 8088 gen started with 16K. I remember this because I bought a CPM80 machine and thought it was a great deal because it came with 64K memory

TTFN

 

[xchnauzer]

Consider changing the auto recalc option in excel to manual.

 

[SacreBleu]

XCH: Manual recalculation is not advisable. I have a "war story" about that.
I tried putting the fixed data on another workbook, but it had little effect.

 

[SacreBleu]

As a final postscript, I tried a third-party performance monitor utility software. It seems the speed of the Excel is mostly governed by CPU processor speed. It doesn't require as much RAM as I thought.