HELP - Excel 2003 VERY SLOW vs 2002



I have an Excel spreadsheet which I would describe as non tirivial but not
too elaborate either that runs fine in Excel 2002 but becomes unbearably slow
doing the simplest of tasks in 2003. Example, opening / closing row groups
can take 30-90 seconds or more in 2003 versus <2 seconds in 2002 for the same
exact spreadsheet. This is a master template that we teach many others to
use so it must run on all machines running 2002 and 2003 - besides newer
Office versions are supposed to be faster not slower RIGHT? So please HELP !

The bulk of the spreadsheet is in a single worksheet that is 60 columns x
2000 rows (~4MB total file size) . There are some OFFSET functions located
within the main spreadsheet and a few on a secondary sheet. I also have cell
drop down menus and conditional formatting used sporadically throughout (~100
cell menus / column and maybe the same for conditional formatting). I have
the problem in Excel 2003 with a version that has simple GoTo macros or
doesn't have macros on, enabled, disabled or deleted entirely. I refuse to
turn Auto Calculation off because that is asking for trouble with people
using bad results. HELP !


More details....
I have a band aid on the problem by creating macro buttons to jump me around
the spreadsheet by turning off caculations jumping to a new location and
turning calculations back on. I think the problem is related to a change in
Excel 2003 where subtotals require a recalculation when rows are hidden or
unhidden. We use 5 levels of row groups throughout this spreadsheet because
otherwise it is impossible to navigate 2000+ rows long so I don't want to get
rid of the row groups. I am open to suggestions, but the options I came up
with are....

1.Is there an event related to opening or closing row or column groups?
where I could shut off the recalc first.

2. Is there a way to turn off this new feature in Excel of recalculating
subtotals based upon visible rows?

Does anyone know how to do either of the above or another way to fix this

This link is where I got my information....

Hiding or Unhiding Rows in Excel 2003
In Excel 2003 hiding or unhding rows will flag the selected rows as
uncalculated, even if no rows were actually hidden or unhidden. If
calculation is automatic this will trigger a recalculation. This behaviour is
a change from previous versions. Hiding or unhiding columns does NOT flag the
column as uncalculated.

Probably the reason for the change is that the SUBTOTAL function in Excel
2003 has an option to include or ignore hidden rows, so Excel needs to be
able to trigger a dependency recalculation by dirtying the cells when a row
is hidden or unhidden.

See evaluation circumstances for the circumstances that will cause a formula
to be evaluated.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question