Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
KMH
 
Posts: n/a
Default 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 !

Background...
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 !

  #2   Report Post  
Posted to microsoft.public.excel.misc
KMH
 
Posts: n/a
Default HELP - Excel 2003 VERY SLOW vs 2002

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
issue.

This link is where I got my information....
http://www.decisionmodels.com/calcsecretsi.htm...

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.


"KMH" wrote:

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 !

Background...
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 !

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 Slow Start Up SysAdmMAX411 Excel Discussion (Misc queries) 1 January 18th 06 07:38 PM
Excel 2002 document not opening with a proper view in Excel 2003 taj New Users to Excel 1 January 17th 06 11:36 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Open file Slow on Excel 2003 Vuka Excel Discussion (Misc queries) 0 March 17th 05 06:19 AM
Macros running slow in Excel 2003 ebeltran Excel Discussion (Misc queries) 0 March 8th 05 09:30 PM


All times are GMT +1. The time now is 02:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"