How to speed up sluggish worksheet???

[Big Blue]

I have a 2000 row spreadsheet with some hefty formulae, including several with 6 nested if stmts. Whne data is entered, it takes a long time for the sheet to run through it's calculations. Anything I can do to spped it up (short of buying a faster CPU)?

[Mark W.]

See http://support.microsoft.com/default...b;EN-US;q72622.

[ This Message was edited by: Mark W. on 2002-04-08 08:34 ]

[Jay Petrulis]

You may want to rethink your entire setup to see if any efficiencies can be gained.

http://spreadsheetstyle.com/

is a great resource for this.

Barring that, you may have a lot of volatile functions or complicated arrays that take a long time to process. It is just a long, slow haul.

If you find yourself having to update data and, after *each* entry the spreadsheet goes through a recalc process, you have to set your Calculation setting to manual

Tools>Options>Calculations (Manual)

then set it back to automatic, or hit F9 (or Ctrl+Alt-F9) to recalculate.

HTH,
Jay

[Big Blue]

Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!

[Big Blue]

Thanks for advice Jay.
My calulations are twofold - one, I strip dates out of serial numbers and post to ther columns to eliminate need for users to re-enter(limiting chance for error). I have extensive summaries subtotalling several aspects all by weeks (start and end dates taking statutory and company holidays into account).
THOUGHT: The summary is a separate sheet. Could I somehow only calculate summary when someone looks at sheet? (ie instead of updating all the subtotals as a user is entering data on log sheet?) !!!!

[Mark W.]

On 2002-04-08 08:44, Big Blue wrote:
Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!

'Automatic Except Tables' won't make any difference unless you're using Data Tables. Have you considered summarizing your data using PivotTables?

[Big Blue]

No, to be honest I've never explored Pivot Tables. Only have a rudimentary understanding of them. If you think they might serve this kind of purpose, I start to research. My current summaries have about a dozen array formulae to provide weekly subtotals of volumes and values.

[GregPascal]

You can go into tools, options ... and set the calculation to manual during data entry. When you are finished, you can then either hit F9 to calculate or set it back to automatic calculation.
Alternately, you can copy-paste-special the values for all rows except the first one, and simply copy the first row's formulas into any new rows when needed. Depends on hoiw much the information in the boady changes with time. This solution is good when the data remain essentially unchamged after they are captured.

[Chris Davison]

Also, when you have some spare time, have a look at Excel's Database functions... they are all prefixed with "D"

DSUM
DCOUNT

etc etc

it (sort of) switches excel into database mode - my non-technical way of thinking

one of my recent 20 meg spreadhseets which took 3 mins to recalculate was smoothed down to 9 megs and just a SECOND to recalculate

depends on what you want though, they're not always a definitive better way....... check them out though

[Big Blue]

Thanks for effort, but I started with D...
and wound up with a horrendous spreadhseet
imagine subtotalling 3 categories of items, by 8 departments, for every week of the year excluding holidays... I had to use arrays