excel stuck in calculating mode

[mhakwatut]

excel 2007 stuck in calculating mode!
Details
-window 7.
-HP elite book with dual processors.
-22MB excel workbook, 15 sheets and tons of sum ifs and count ifs.
-workbook is shared across an enterprise network for ~15 folks to work on.

problems
-workbooks takes 12-15 minutes to open.
-when trying to open or save it appears the book is trying to re-calculate all the formulas and the processor usage explodes.

suggestions?

 

[Steve=True]

First, Change your calculation mode:

Excel Office button> Excel Options> Formulas> Manual Calculation Option


Second, you may want to find out what calculations are taking so long.

For instance, if you have a running sum like this:

=sum($b$2:b20000)

vs

=b3+c2

you may be causing some of your performance problems (Probably the count ifs on a large data set. Perhaps Sumproduct could be faster)

Mr. Excel Posted this link once:
http://msdn.microsoft.com/en-us/library/aa730921.aspx


Steve=True
www.exceldashboardtemplates.com

 

[mhakwatut]

What appeared to be calculating mode acturally turned out to be ~20MB of bloat due to sharing the work book.
When loading the book, my laptop CPU idle % went to nil.
Resolved by un sharing. Was really amazing to see the file size drop so much!
Thanks to all.