"Calculating Cells" Mystery

[WildWill]

Hi

I created a spreadsheet of about a total of 90 columns and 900 lines, spread
accross 4 worksheets. The spreadsheet does basic calculations like
SUMPRODUCT, etc. For some reason, whenever I make ANY change to the
sreadsheet, i.e. entering new data, etc, etc I get a message in the bottom
left corner of the Excel window, saying "Calculating Cells - plus a %
indicator, which then runs from 0% to 100%. It takes about 20 seconds for
this little % indicator to run to 100%, and while this message is active, the
database becomes "locked" and you can't select any other cells or input any
other data. I is extremely annoying - it makes data entry very frustrating
and this is further aggrivated by the large volumes on data that I am feeding
into this database. What have I done to have this happen? I have never seen
this before in any of the spreadsheets that I have created in Excel? How do I
take this away?

 

[Stefi]

Your functions - especially SUMPRODUCT - give Excel too much calculation
work. If calculation mode is set to automatic (default) Excel recalculates
all cells. You have two options:
1. reduce volume and/or complexity of functions
2. set calculation mode to manual (Tools>Options>Calculation tab), after
finishing data entry you can trigger recalculation by pressing F9

Regards,
Stefi


„WildWill†ezt írta:

 

[Fred Smith]

You have created a spreadsheet that takes Excel some time to recalculate.
Your options are:

1. Change recalculation to manual. Then you can enter data without pausing,
and manually recalculate (F9) when you're done.
2. Buy a faster computer.
3. Simplify your calculations. 90x900 is not a very large spreadsheet, so
you must have a lot of calculations, or they are complicated. Can you change
some of your calculations to values, if they never have to be done again?
Can you use a Pivot Table, rather than Sumproducts -- PTs are much faster.
Can you reduce the range sizes you are using? Other suggestions for speeding
up Excel spreadsheets can easily be found on the net.

Regards,
Fred.