Suppressing calculation of some worksheets

[Gastón]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I have a very large workbook composed of up to 12 worksheets, all very heavy (numerical integrations). Oftentimes I don't use all of them, e.g. just 4 or 5. How can I suppress re-calculation of the spreadsheets that are not being used in order to speed things up?

Also, how can I set up Excel or OSX (10.4.11) system to allocate all available RAM memory to Excel and thereby speed up Excel?

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I have a very large workbook composed of up to 12 worksheets, all very heavy
(numerical integrations). Oftentimes I don't use all of them, e.g. just 4 or
5. How can I suppress re-calculation of the spreadsheets that are not being
used in order to speed things up?

Also, how can I set up Excel or OSX (10.4.11) system to allocate all available
RAM memory to Excel and thereby speed up Excel?

If the workbooks are open, you can not select which are calculated. However,
if calculation is set to manual, you can calculate only the current
workbook. Realize that doing so may produce incorrect results if some of the
intermediate workbooks are not calculated.

Excel and OSX are pretty good about allocating memory and CPU priority. You
can always assume that all "available" memory is being efficiently used. You
may be able to improve performance by making sure that any other
applications are not open. Adding more RAM may increase performance if there
is a lot of paging in your current configuration. You can monitor this with
the Activity monitor.

 

[JE McGimpsey]

Excel and OSX are pretty good about allocating memory and CPU priority. You
can always assume that all "available" memory is being efficiently used. You
may be able to improve performance by making sure that any other
applications are not open. Adding more RAM may increase performance if there
is a lot of paging in your current configuration. You can monitor this with
the Activity monitor.

I've found that some applications take up an inordinately large amount
of CPU. Parallels is especially greedy when you set its preferences to
maximize allocation to it.

I have gotten a *small* speed boost by renice-ing the XL process to -20
(see http://en.wikipedia.org/wiki/Nice_(Unix)) in the Terminal, or via
the Renicer app.

Also, while it doesn't happen often, I've had times when operating
system swap files got so numerous and so huge that the resulting paging
slowed everything down. Restarting the machine deletes all the existing
swap files and restores performance.

If your worksheets are indeed "heavy" with calculations, you will
probably see the greatest speed boost by ensuring that intermediate
calculations are performed once, rather than multiple times per cycle.

For a very simple example, if you have

A1: =SUM(A1:Z1)*2
A2: =SUM(A1:Z1)*3
A3: =SUM(A1:Z1)*4

you can cut calculation time by 1/2 or more using

AB1: =SUM(A1:Z1)
A1: =AB1 * 2
A2: =AB1 * 3
A3: =AB1 * 4

Or, alternatively, use a defined name (Insert/Name/Define):

Name: MySum
Refers to: =SUM(Sheet1!$A$1:$Z$1)

A1: =MySum * 2
A2: =MySum * 3
A3: =MySum * 4

For the above examples, you'd need to figure out what to do with the 20
milliseconds or so that you'd save, but if you have lots of repetitive
calcs, you can get a *lot* more efficient by using intermediate calcs.